[Solved] problem with vlookup because abbreviation

[reza_akbar]

i have problem with vlookup because they use abbreviation
the data that i get it use abbreviation and the source that i get it it don't use abbreviation

 

[reza_akbar]

vlookup with true didn't solve the problem, i dont know how big is the data, it just small example my problem

 

[Lutfor Rahman Shimanto]

vlookup with true didn't solve the problem, i dont know how big is the data, it just small example my problem

Welcome to our platform, Reza Akbar. The VLOOKUP-TRUE argument is mainly used when we work with numeric data. TRUE, as the last argument of the VLOOKUP function, will return an approximate match of the lookup value. In your demonstration, I recommend you to go for the exact match, in this case, FALSE. I am implementing a method to resolve your issue using an exact match in the VLOOKUP function. Hopefully, the procedure will be completable with a large dataset.

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

i have problem with vlookup because they use abbreviation
the data that i get it use abbreviation and the source that i get it it don't use abbreviation

Hello Reza Akbar,

Thanks a ton for reaching out. By exploring your provided workbook, I understand that the look_up values may contain an abbreviation, but the source data does not. That is a serious problem indeed. You can remove the first word from the look_up value and store it in a Helper Column to overcome the issue. Later, use that Helper Column with an Asterisk (*) sign to resolve the look_up issue. You can solve the problem without a helper column as well. In this case, you must apply the formula used in the helper column within the VLOOKUP formula. The formula I am applying looks complex. If you are interested in how these work, I will explain later. I am showing you both of these approaches and attaching the Workbook to help you understand the problem better.

=TRIM(RIGHT(SUBSTITUTE($E$14," ",REPT(" ",LEN($E$14))),(LEN($E$14)-LEN(SUBSTITUTE($E$14," ","")))*LEN($E$14)))

The formula is responsible for removing the abbreviation.

Method-1 VLOOKUP Formula:

=VLOOKUP("*"&$C$12,$G$2:$I$10,1,FALSE)

​

Method-2 VLOOKUP Formula:

=VLOOKUP("*"&TRIM(RIGHT(SUBSTITUTE($E$12," ",REPT(" ",LEN($E$12))),(LEN($E$12)-LEN(SUBSTITUTE($E$12," ","")))*LEN($E$12))),$G$2:$I$10,1,FALSE)

​

Please let me know if you have any further questions or if there's anything else I can assist you with.

Regards,
Lutfor Rahman Shimanto

 

[reza_akbar]

vlookup work with Asterisk (*)? how its work??

edited:

i see, that how its work,
asterik also work with middle name

 

[Lutfor Rahman Shimanto]

vlookup work with Asterisk (*)? how its work??

edited:

i see, that how its work,
asterik also work with middle name

Great to hear that the solution worked perfectly and you understand the mechanism of the formulas, Reza Akbar! The VLOOKUP function can work with an Asterisk (*) as a wildcard character in the lookup value. We can use the Asterisk (*) sign at the lookup value's beginning, middle, or end or in combination with other characters to create more complex search patterns.

Please do not hesitate to contact us with any additional questions or problems regarding Excel.

Regards
Lutfor Rahman Shimanto