vlookup with preserve carriage return

[mu4you]

Hello freinds,

I have around 500 Rows and some cells are as shown below and I need to get the values by using Vlookup, any masters there who can guide me. Thanks in advance.

 

[NARAYANK991]

Hi ,

Please upload your workbook with enough data in it.

Narayan

 

[NARAYANK991]

Hi ,

Otherwise , try this :

=VLOOKUP("*" & F1 & "*",A1:B1,2,FALSE)

Going by what you have posted , instead of F1 , it should be D1.

Narayan

 

[mu4you]

First, thanks a lot for your reply and please find attached sample file. hope it's clear.

 

[bosco_yip]

Try,

C2, formula copy down :

=LOOKUP(1,-SEARCH(B2,A$12:A$14),B$12:B$14)

Regards
Bosco

 

[mu4you]

Try,

C2, formula copy down :

=LOOKUP(1,-SEARCH("*"&B2&"*",A$12:A$14),B$12:B$14)

Regards
Bosco

SIR,

great respect !!!!! great, great , great. I was dying with multiple vlookups,index and Match....

are you robot ..

thanks once again....

 

[bosco_yip]

SIR,

great respect !!!!! great, great , great. I was dying with multiple vlookups,index and Match....

are you robot ..

thanks once again....

Hi mu4you,

The above formula "*" can be removed and become :

=LOOKUP(1,-SEARCH(B2,A$12:A$14),B$12:B$14)

Regards

 

[mu4you]

Thanks again sir,

in my original data, I have some blank cells in B2, I applied If(isblank) function in your formula but is not working..... any suggestions ?

 

[bosco_yip]

Thanks again sir,
in my original data, I have some blank cells in B2, I applied If(isblank) function in your formula but is not working..... any suggestions ?

Try to replace with this revised formula.

=LOOKUP(1,-SEARCH(SUBSTITUTE(B2," ",""),A$12:A$14),B$12:B$14)

If the formula don't suit to work, please post back a file with details.

Regards
Bosco

 

[Asheesh]

It works fine when I tried using ISBLANK..(as per the attached in comment no 5)

Did you try it on the same lines as mentioned below

IF(ISBLANK(B2),"",LOOKUP(1,-SEARCH(B2,A$12:A$14),B$12:B$14))

 

[mu4you]

Dear bosco nd Ashlee

Previously the cells were not blank nd when i cleaned them the isblank perfectly working but i found that there are some duplicates...i will send the sheet on sunday because friday nd saturday is weekend here in middle east.

thanks guys...

 

[Enrique Candelas]

Hi there,

See attached your original workbook modified by me using VLOOKUP function.

It covers blank values, empty strings ("") and new values with no match.

Regards.

 

[mu4you]

great sir,

thanks a lot. I am very new to this type of complicated formula's. if possible just give some small explanation of this formula.

thanks again.

 

[Enrique Candelas]

Hi mu4you,

The magic is located within the first parameter (string to look up) of VLOOKUP function such as NARAYANK991 told you but including TRIM function on source cell for isolating the value to look up. The asterisc sign (*) made the rest of it.

VLOOKUP("*" & TRIM(B2) & "*";$A$14:$B$16;2;FALSE)

By other hand, the formula check if the result is not match then return the string "???" or whatever you want ...

IF(ISERROR(VLOOKUP("*" & TRIM(B2) & "*";$A$14:$B$16;2;FALSE));"???";...)

At last (but at begining of the formula), it checks if eliminating any spaces that source cell could get into was an empty (not null) string ("") or also if the source cell is blank (null) then returns the string "???" or whatever you want, in any of these cases. Otherwise, it checks the 2nd IF sentence such as I have mentioned above.

=IF(OR(TRIM(B2)="";ISBLANK(B2));"???";...)

I hope it helps you in your current and future Worksheet's development.

Regards to all.

 

[mu4you]

Wor, you are majestic !!!

No words so say thanks for your great help and explanation !!

Thanks once again.