URGENT need help - partial match to get answer excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3124232%22%20slang%3D%22en-US%22%3EURGENT%20need%20help%20-%20partial%20match%20to%20get%20answer%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3124232%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20data%20table%20and%20a%20legend%20table%3C%2FP%3E%3CP%3EI%20need%20to%20partial%20match%20the%20title%20(first%20about%2015%20characters%20to%20the%20legend%20table%20title%20)%20to%20get%20back%20the%20CL%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20try%20vlookup%20%2C%20index%20match..%20I%20just%20cannot%20solve%20it%20as%20the%20data%20is%20hugh%20very%20title%20of%20various%2C%20can%20I%20based%20on%20the%20first%2015%20to%2018%20characters%20to%20match%20back%20to%20the%20legend%20to%20get%20the%20Cl%20column%20(answer)%20from%20the%20legend%20back%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%26nbsp%3B%20Thank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3124232%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-3125678%22%20slang%3D%22en-US%22%3ERe%3A%20URGENT%20need%20help%20-%20partial%20match%20to%20get%20answer%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3125678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612792%22%20target%3D%22_blank%22%3E%40bbsin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(%22*%22%26amp%3BLEFT(B2%2C15)%26amp%3B%22*%22%2CCHOOSE(%7B1%2C2%7D%2CLegend!%24C%242%3A%24C%2420%2CLegend!%24B%242%3A%24B%2420)%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20want%20to%20return%20the%20contribution%20level%3F%20This%20could%20be%20possible%20with%20above%20formula.%20Enter%20the%20formula%20as%20arrayformula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

hi

 

I have a data table and a legend table

I need to partial match the title (first about 15 characters to the legend table title ) to get back the CL numbers.

 

I try vlookup , index match.. I just cannot solve it as the data is hugh very title of various, can I based on the first 15 to 18 characters to match back to the legend to get the Cl column (answer) from the legend back?

 

Please help.  Thank you

 

Attached file

6 Replies

@bbsin 

=VLOOKUP("*"&LEFT(B2,15)&"*",CHOOSE({1,2},Legend!$C$2:$C$20,Legend!$B$2:$B$20),2,FALSE)

 

Do you want to return the contribution level? This could be possible with above formula. Enter the formula as arrayformula with ctrl+shift+enter if you don't work with Office365 or 2021.

Hi @Quadruple_Pawn,

Thank you for your kind reply. Yes I want to return the CL. I try putting the formula to the sheet, it show NA? I might have done something not right.

My data is in one worksheet and the legend in other worksheet. There's one more column of information in the legend before the CL and Title.

May I know does CHOOSE({1,2} stands for?

Thank you

hi, i m a excel learner and curious to know & discuss, i wonder why you used "*"& in lookup argument of vlookup rather than only left function? i tried with only left function & didn't recieve any results.
i would appreciate if you help me out of it.

harshulz_0-1644297740685.png

 

@bbsin 

Does this mean that contribution level is in column C and position is in column D of the Legend sheet?

 

You would need to adapt the formula according to the actual layout of your data.

 

=VLOOKUP("*"&LEFT(B2,14)&"*",CHOOSE({1,2},Legend!$D$2:$D$20,Legend!$C$2:$C$20),2,FALSE)

 

This formula works in the attached file. If you don't work with Office 365 or 2021 you have to enter the formula with ctrl+shift+enter.

 

In this example CHOOSE({1,2} allows within the VLOOKUP to search for the search value in range Legend!$D$2:$D$20 and to return the corresponding value from range Legend!$C$2:$C$20.

@harshulz 

If you enter "*"&LEFT(B2,14)&"*" as search value VLOOKUP performs a partial match. For example the search value "Senior manager" would be found in the search array within the strings "Senior manager assistant" and "Executive Senior manager director".

 

Search value LEFT(B2,14) would mean that VLOOKUP performs an exact match. There would only be a search result if e.g. search value is "Senior manager" and the string "Senior manager" is found in the search array.

ooh that way, now i get it, thank you quadruple.