indexing - Excel Index Match with 3 criteria but two columns could move

Question

I have a large table of data but to make it simple using the following table how would I get "money" based on site and description? But here's the trick..the table is copied and pasted in the sheet and sometimes description,target and money are flipped around.

So how would I select a column using INDEX MATCH based on the header? Site is normally static in A:A but if there is a way to make that dynamic that would be amazing!!

Answer 1

You can use INDEX MATCH to return a column out of a table to pass into another INDEX MATCH

For your sample data try

=INDEX(INDEX($A:$Z,,MATCH(B14,$1:$1,0)),MATCH(C12,INDEX($A:$Z,,MATCH(B12,1:1,0)),0))

Ensure the inner INDEX range is large enough to cover your table wherever it is (I've used $A:$Z in this example)

Breaking it down, INDEX($A:$Z,,MATCH(B12,1:1,0)) returns the column containing the header value in cell B12 (Site in this case).

Similarly for INDEX($A:$Z,,MATCH(B13,$1:$1,0)) and money

Just make sure the labels in B12, B13 match the table headers exactly (ie Site, not site:)

---

Based on comment, to match site and description, try

=INDEX(INDEX($A:$Z,,MATCH(B14,1:1,0)),MATCH(1,(INDEX($A:$Z,,MATCH(B13,1:1,0))=C13)*(INDEX($A:$Z,,MATCH(B12,1:1,0))=C12),0))

entered as an array formula (press Ctrl-Shift-Enter rather than just Enter)