vlookup or index want to use wildcard

[ashabc]

My objective is to fill up the column R in the "main" tab with values from the column B in the "lun" tab in an excel workbook with some matching criteria (possibly vlookup/index etc).


e.g. I want to take the content of O2 cell ("main" tab) and then search it in the contents in column E (of "lun" tab). If matches (not necessary the exact match, as long as long as it contains the content of O2), I would like to return the value from the column B ("lun" tab) from that matching row and place it in R2 ("main" tab).


So, if works properly,


R2 ("main" tab) should contain the value from B377 ("lun" tab) [because E377 (lun tab) contains “naa.6006016040a02c00343a8d58eeeee011”, which is the content of O2(main tab)]


R3 ("main" tab) should contain the value from B306 (of "lun" tab) and so on.


I tried something like this, but it does not work.


=VLOOKUP("*"&O2&"*",lun!A:E,2,TRUE)

=INDEX(lun!B:B,MATCH(Q2,lun!E:E,0))


Not sure if I can use wildcard with index.


Any idea what should I try? I can't see a way to attach an excel sheet with the post, it could be handy.

 

[NARAYANK991]

Hi ,


Try this :


=INDEX(lun!B2:B1000,MATCH("*"&O2&"*",lun!E2:E1000,0))


where I have assumed that your data range is B2:E1000.


Narayan

 

[ashabc]

Thank you Narayan.


I tried the wildcard with MATCH as suggested by you. Still doesn't work.

The result is #N/A


When I do the "Show the calculation steps", I can see that its translating to:


INDEX(lun!B2:B1000,MATCH("*"&O2&"*",lun!E2:E1000,0))

INDEX(lun!B2:B1000,MATCH("*naa.6006016040a02c00343a8d58eeeee011

*",lun!E2:E1000,0))

INDEX(lun!B2:B1000,#N/A)

#N/A


So, it seems that the MATCH function is taking up the right value to search, but unable to find the match!


I can confirm that row 377 of column E of lun tab has got "naa.6006016040a02c00343a8d58eeeee011". Though, the number of characters of E377 cell is 360. Could it be a problem? I have copied the contents of the cell E377 below.


SVHOF-ESX06 - naa.6006016040a02c00343a8d58eeeee011; SVHOF-ESX01 - naa.6006016040a02c00343a8d58eeeee011; SVHOF-ESX05 - naa.6006016040a02c00343a8d58eeeee011; SVHOF-ESX04 - naa.6006016040a02c00343a8d58eeeee011; SVHOF-ESX03 - naa.6006016040a02c00343a8d58eeeee011; SVHOF-ESX02 - naa.6006016040a02c00343a8d58eeeee011


May be I have to reduce the number of characters by using LEFT/RIGHT function first. because it has multiple occurrences of "naa.6006016040a02c00343a8d58eeeee011

", so still could work for me.


Note sure.

 

[ashabc]

Got it working! I used


=INDEX(lun!B2:B1000,MATCH("*"&O2&"*",LEFT(lun!E2:E1000,105),0))


So, I think its a bug in excel! It cannot handle long string (in my case 360 character long)


Thank you for your help Narayan.