SOLVED

VLOOKUP formula - match two columns (partial) and return value

%3CLINGO-SUB%20id%3D%22lingo-sub-2561185%22%20slang%3D%22en-US%22%3EVLOOKUP%20formula%20-%20match%20two%20columns%20(partial)%20and%20return%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2561185%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20there.%26nbsp%3B%3CBR%20%2F%3EI%20am%20facing%20an%20issue%20trying%20to%20match%20two%20columns%20and%20return%20value%20to%20third%20with%20the%20Vlookup%20formula.%26nbsp%3B%3CBR%20%2F%3EThe%20idea%20is%20to%20do%20an%20approximate%20name%20match%20and%20get%20the%20correct%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Tom_Khachatryan_0-1626678901875.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296697i14978F7A46A8B2A4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Tom_Khachatryan_0-1626678901875.png%22%20alt%3D%22Tom_Khachatryan_0-1626678901875.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20appreciate%20your%20help.%26nbsp%3B%3CBR%20%2F%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2561185%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2561206%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20formula%20-%20match%20two%20columns%20(partial)%20and%20return%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2561206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1106851%22%20target%3D%22_blank%22%3E%40Tom_Khachatryan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20work%20for%20you%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(LEFT(C8%2CFIND(%22%5E%22%2CSUBSTITUTE(C8%2C%22%20%22%2C%22%5E%22%2C3))-1)%26amp%3B%22*%22%2C%24J%243%3A%24K%2429%2C2%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2561207%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20formula%20-%20match%20two%20columns%20(partial)%20and%20return%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2561207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%2C%20it%20is%20superb.%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2561370%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20formula%20-%20match%20two%20columns%20(partial)%20and%20return%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2561370%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1106851%22%20target%3D%22_blank%22%3E%40Tom_Khachatryan%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2565353%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20formula%20-%20match%20two%20columns%20(partial)%20and%20return%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2565353%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%2C%20I%20tried%20the%20same%20formula%20in%20another%20sheet%2C%20but%20still%20getting%20the%20same%20error.%20I%20can%20not%20understand%20the%20core%20of%20this%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Tom_Khachatryan_0-1626779119790.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297050i87F25D3C25C00431%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Tom_Khachatryan_0-1626779119790.png%22%20alt%3D%22Tom_Khachatryan_0-1626779119790.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2573613%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20formula%20-%20match%20two%20columns%20(partial)%20and%20return%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2573613%22%20slang%3D%22en-US%22%3EPlease%20pay%20attention%20to%20the%20lookup%20value%20in%20the%20formula%2C%20the%20values%20in%20lookup_column%20in%20the%20table_array%20must%20start%20with%20the%20lookup%20value.%3C%2FLINGO-BODY%3E
New Contributor

Hello there. 
I am facing an issue trying to match two columns and return value to third with the Vlookup formula. 
The idea is to do an approximate name match and get the correct value. 

Tom_Khachatryan_0-1626678901875.png

 

Will appreciate your help. 
Thank you

 

6 Replies
best response confirmed by Tom_Khachatryan (New Contributor)
Solution

@Tom_Khachatryan 

Does this work for you?

 

=VLOOKUP(LEFT(C8,FIND("^",SUBSTITUTE(C8," ","^",3))-1)&"*",$J$3:$K$29,2,0)

You're welcome @Tom_Khachatryan! Glad it worked as desired.

Hello @Subodh_Tiwari_sktneer , I tried the same formula in another sheet, but still getting the same error. I can not understand the core of this issue. 

Tom_Khachatryan_0-1626779119790.png

 

Please pay attention to the lookup value in the formula, the values in lookup_column in the table_array must start with the lookup value.
In your Vlookup formula, you are using 0 as the last argument which is used for an exact match. Use 1 instead as you want to do an approximate match.