Partial Vlookup Wildcard not finding match but returning #N/A

[acejackhammer]

Hello there. I have two worksheets. One with partial data (example = 80151006) and the other worksheet with entire data (example = Lif HCCCLAIMPMT 186027 0063143 TRN *1* 0080151006*124389766). I used the formula =VLOOKUP(A7&"*",database!$A$2:$A$227,1,FALSE). And all I get is the #NA in the formula cell. I've tried to change the format to the source data to general, text, number on both worksheets and still get that dreaded #N/A.

What am I doing wrong?

 

[PhilS2520]

Hi acejackhammer

When you use FALSE in the VLOOKUP function, that means you are looking for EXACT matches.

What I think you should do is (if the data is the same, that is the string you are trying to find is allways starting at character position x, and is y characters long, that you use the MID() function as the lookup value.

If not, that is the data is not always the same, use the FIND() or SEARCH() functions to get the VLOOKUP(‘ function search term.

hope this helps.

Sorry, I meant to add this: you need to make sure that you are searching for the same type of data. That is text or string vs. numbers. The VLOOKUP() will need to have the dataset sorted as well.

 

[shaowu459]

try this:

=LOOKUP(1,0/FIND(8015100,A1:A6),B1:B6)

A1:A6 stored text lik "Lif HCCCLAIMPMT 186027 0063143 TRN *1* 0080151006*124389766", B1:B6 stored what you want to return.

 

[Aladin Akyurek]

You need wildcarding for 'contains', not just 'starts with..

=VLOOKUP("*"&A7&"*",database!$A$2:$A$227,1,FALSE)

Another option:​

=LOOKUP(9.99999999999999E+307,SEARCH(A7,database!$A$2:$A$227),database!$A$2:$A$227)