Index with 3 Matches

[BenGee]

Hi - Could someone kindly help me work out the below (a little stuck with it);

=INDEX(Sheet1!AG2:AK36,MATCH(A10,Sheet1!A2:A421,0),MATCH(A1,Sheet1!AF2:AF421,0),MATCH(B9,Sheet1!AG1:AK1,0))

Been messing around with it for ages and just can't get it to work. Thanks in advance!

 

[steve the fish]

Its a bit mixed up. How big is your index range meant to be? At present its 35 rows and 5 columns. Your first match is confusing. You are trying to match a value A10 which is present within the lookup array.

Edit...Sorry just seen the Sheet1 part.

 

[steve the fish]

Lets try this and see how you go:

=INDEX(Sheet1!AG2:AK421,MATCH(1,INDEX((Sheet1!A2:A421=A10)*(Sheet1!AF2:AF421=A1),0),0),MATCH(B9,Sheet1!AG1:AK1,0))

 

[Marcelo Branco]

I'm not sure what you need but it seems you do want to find a row in Sheet1 wich contains A10 in A2:A421 and A1 in AF2:AF421 and which column in the range AG1:AK1 is equal to B9.

If so, maybe this array formula

=INDEX(Sheet1!AG2:AK421,MATCH(1,IF(Sheet1!A2:A421=A10,IF(Sheet1!AF2:AF421=A1,1)),0),MATCH(B9,Sheet1!AG1:AK1,0))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[BenGee]

steve the fish - works wonderfully thank you.

Marcelo, thanks for help also