Index / Match headache

[AlexH]

I have a need for a user to select a group and a day (see attached sheet) and for the formula return the groups of letters on the right.

For example select Group 1 & Day 2 would return
TEAIM AAETN INATE EAINI EITNI MIETA etc


When I have a single criteria I would use a lookup (hlookup in this case) but now I have 2 criteria I am led to think that INDEX & MATCH as an array is the right thing to use. But try as I might I cannot get this to work.

Can you help?

 

[teylyn]

If the desired group is specified in a cell with the range name TheGroup, the desired day is specified in a cell with the name TheDay, then this formula will return the correct value from column C

=INDEX($C$1:$AA$14,MATCH(TheGroup&TheDay,INDEX($A$1:$A$14&$B$1:$B$14,0),0),1)

Change the value before the last closing parens to a different number to return a different column.

cheers, teylyn