How could I make VLOOKUP() work somewhat like this?

=VLOOKUP(Z10,Sheet1!A5:Z100,COLUMN(MATCH("ID",Sheet1!A5:5,0)),0)

...where the col index is obtained by matching the column header's with what's entered there.

Thank you!

CodePudding user response：

should be:

=VLOOKUP(Z10, Sheet1!A5:Z100, MATCH("ID", Sheet1!A5:5, 0), 0)

where

=MATCH("ID", Sheet1!A5:5, 0)

match will output the number of column. lets say ID header is in E5. that's 5th column of range A5:5 so the output will be 5, therefore, vlookup will output the 5th column from A5:Z100 which is E column. summary: look for Z10 in A5:Z100 and if Z10 is found in A5:A100 range, output the matching column with header ID