I have a spreadsheet (MASTER TIMETABLE) listing a number of venues, days and activities on each day, as shown below:
In a separate sheet, I want to provide inputs of the Venue and Day, and get an output of the Activity. For example, if I input VIC - Kennington (B20) and Thursday 5th April (J20), I'd get The Zone (J21) as my output.
My problem is that the days change from venue to venue, so I can't just highlight the entire array and use the top row to lookup the Day. I've been able to find out the row to search by using the following formula:
=MATCH($B$2,'MASTER TIMETABLE'!B:B,0)Using this, if B2 is VIC - Kennington, I'll get an output of 20. That gives me the row I then need to search for the Day, but I can't seem to figure out how to then pull that into the next MATCH function, e.g.:
=MATCH($A$5,'MASTER TIMETABLE'!20:20,0)If I can figure out how to generate the 20:20 part of the formula dynamically, I know that the next step is just a simple offset down by one row, but I can't figure out how to get the formulas to nest. I've also considered using VLOOKUP, but again I can't figure it out.
1 Answer
I've managed to get it to work, using INDEX. Considering the Day columns don't move in relation to the Venue, I can just set the column_num as a static. Nesting that inside an OFFSET gives me the Activity. Here's my final formula:
=OFFSET(INDEX('MASTER TIMETABLE'!$B$2:$Z$667,MATCH($B$2,'MASTER TIMETABLE'!$B$2:$B$666,0),5),1,0)Thanks to Bandersnatch for putting me on the right track.
1
