Search code examples
arraysgoogle-sheetsgoogle-sheets-formulamatchvlookup

How to make Vlookup index column dynamic?

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!

Solution

  • 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