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!!
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)
