Having trouble with advanced INDEX MATCH function

[Rocket17]

Hello Forum, Thank you for reading my first post.

I have been trying to figure out a particular problem for the better part of the afternoon; here is the challenge:

I need to pull data from a workbook based on multiple column criteria, as well as a column criteria as well. I have gotten pretty close using INDEX MATCH, however I just cannot seem to quite get it right... The reason there are two column criteria is because column A holds names and each name will repeat multiple times. Column B holds items that also repeat, but never repeat for column A. For example:

A B
1 Name1 Item1
2 Name1 Item2
3 Name1 Item3
4 Name2 Item1
5 Name2 Item2
6 Name2 Item3

Now I need to match the exact row based on the unique pair of both columns. Then I need to also match the column that I am finding the value based on its heading. As the user selects criteria in a filters, the table Column headings act like an accordion, so specific columns may jump from column L to Q with one selection. I need to get the number where the unique row that matches given criteria in column A & B intersects with the Column in a specific range that meets given criteria as well. Hopefully this makes sense. Thanks in advance.

[FlameRetired]

Hi RC_Tide17. Welcome to the forum.

Try array entering this formula as in D2 below and criteria in D1:E1. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

	A	B	C	D	E
1				Name2	Item3
2	1	Name1	Item1	6
3	2	Name1	Item2
4	3	Name1	Item3
5	4	Name2	Item1
6	5	Name2	Item2
7	6	Name2	Item3

[Saba Sabaratnam]

Index and Match with multiple row criteria.PNG

Try array formula (Control + Shift + Enter) entered into yellow cell in the attached image.

=INDEX($C$2:$D$7,MATCH(1,(G2=A2:A7)*(G3=B2:B7),0),MATCH(G4,C1:D1,0))

It is the modification of Dave's formula to enable you to select a column from which you can pick up value. Thank you Dave

Kind regards

Saba