Index with 3 Matches

BenGee

Board Regular
Joined
Mar 5, 2016
Messages
195
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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Last edited:
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top