Formula Help - Index/Match with Multiple Columns

I have two sheets that I'm trying to connect. I'm sure there is a simple solution, but I'm brain dead!

SHEET 1: Course Grid - this sheet contains all session codes listed by date/time (columns) and Room names (rows)

SHEET 2: Course List - this sheet has all the information about each session (1 session per row).

I want to be able to bring the Room Name in sheet 1 into the Location column in Sheet 2 - matching on the Session Code.

I recall seeing a demo where you can select a full range of columns when selecting a cross-sheet reference range, but not sure which formula option would work to get what I need!

@Paul Newcome - I'm sure you've answered this before!!! Help!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

@skarkhoff_TR My apologies that I didn't see this before.

My suggestion would be to insert a multi-select dropdown column on the Course Grid with this formula:

=JOIN(COLLECT([First Session Column]@row:[Last Session Column]@row, [First Session Column]@row:[Last Session Column]@row, @cell <> ""), CHAR(10))

Then the formula to bring over the location based on the session name would be

=INDEX(COLLECT({Course Grid - Room name Column}, {Course Grid New Column}, HAS(@cell, [Session Code]@row)), 1)

All comments

Georgie

Hi @skarkhoff_TR,

I noticed this post is from a while ago. Were you able to find an answer, or are you still looking for help with this?

Thanks,

Georgie

Paul Newcome

@skarkhoff_TR My apologies that I didn't see this before.

My suggestion would be to insert a multi-select dropdown column on the Course Grid with this formula:

=JOIN(COLLECT([First Session Column]@row:[Last Session Column]@row, [First Session Column]@row:[Last Session Column]@row, @cell <> ""), CHAR(10))

Then the formula to bring over the location based on the session name would be

=INDEX(COLLECT({Course Grid - Room name Column}, {Course Grid New Column}, HAS(@cell, [Session Code]@row)), 1)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.