INDEX MATCH MATCH to OTHER WORKSHEET

Matt Chappell

Hi,

I'm attempting to perform an INDEX(MATCH(),MATCH()) operation to match the column headers with a row value from another sheet as one would in Excel or Google Sheets.

How do I reference the columns as a range and a value?

The formula should be as follows:

(in cell B2) =INDEX(Sheet1!,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$2,Sheet1!$B:$B,0))

An INDEX(MATCH) or VLOOKUP for each individual column is not an acceptable answer as there are tens and tens of columns :)

Thanks!

Paul Newcome

You would need to have a helper row in your data sheet that contains the column names. Then you would use something along the lines of...

=INDEX({Range to pull from}, MATCH([Column A]@row, {Other Sheet Column A}, 0), MATCH([Column B]@row, {Other Sheet Helper Row}, 0))