Forum Discussion

calijim123's avatar
calijim123
Copper Contributor
May 23, 2019

VLOOKUP when col_index_num is unknown, select col_index_num using a header string

I have very large source table_arrays and the cols are not always in the same position.

Can I select the "col_index_num" using a string instead of a col number?

Thanks.

 

=VLOOKUP(lookup_value,table_array,col_index_num,false)

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match).

excel

3 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 23, 2019
    You can use MATCH to return the col_index_num argument of VLOOKUP.
    • calijim123's avatar
      calijim123
      Copper Contributor
      May 24, 2019

      Twifoo 

       

      That worked. Thanks!

      =VLOOKUP($A2,sheet1!$A:$CE,MATCH(sheet1!B$1,sheet2!$A$1:$CE$1,0),FALSE)

       

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        May 24, 2019
        I’m glad to know that worked.