Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-639020%22%20slang%3D%22en-US%22%3EVLOOKUP%20when%20col_index_num%20is%20unknown%2C%20select%20col_index_num%20using%20a%20header%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-639020%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20very%20large%20source%20table_arrays%20and%20the%20cols%20are%20not%20always%20in%20the%20same%26nbsp%3Bposition.%3C%2FP%3E%3CP%3ECan%20I%20select%20the%20%22col_index_num%22%20using%20a%20string%20instead%20of%20a%20col%20number%3F%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(lookup_value%2Ctable_array%2C%3CSTRONG%3Ecol_index_num%3C%2FSTRONG%3E%2Cfalse)%3C%2FP%3E%3CP%3E%3DVLOOKUP(lookup%20value%2C%20range%20containing%20the%20lookup%20value%2C%20the%20column%20number%20in%20the%20range%20containing%20the%20return%20value%2C%20optionally%20specify%20TRUE%20for%20approximate%20match%20or%20FALSE%20for%20an%20exact%20match).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-639020%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-642640%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20when%20col_index_num%20is%20unknown%2C%20select%20col_index_num%20using%20a%20header%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642640%22%20slang%3D%22en-US%22%3EYou%20can%20use%20MATCH%20to%20return%20the%20col_index_num%20argument%20of%20VLOOKUP.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644547%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20when%20col_index_num%20is%20unknown%2C%20select%20col_index_num%20using%20a%20header%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked.%20Thanks!%3C%2FP%3E%3CP%3E%3DVLOOKUP(%24A2%2Csheet1!%24A%3A%24CE%2CMATCH(sheet1!B%241%2Csheet2!%24A%241%3A%24CE%241%2C0)%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644598%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20when%20col_index_num%20is%20unknown%2C%20select%20col_index_num%20using%20a%20header%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644598%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20glad%20to%20know%20that%20worked.%3C%2FLINGO-BODY%3E
calijim123
New Contributor

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

3 Replies
You can use MATCH to return the col_index_num argument of VLOOKUP.

@Twifoo 

 

That worked. Thanks!

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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies