Formula Help - Index/Match with Multiple Columns

skarkhoff_TR

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!

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)