VLOOKUP returns #NA! on wildcard search

T

theromex

New member
Joined
Feb 13, 2013
Messages
5
Reaction score
0
Points
0
I'm trying to use the following formula to find a value of SIPK0SIA1 on the cell range bellow but it comes up with #NA!. Any ideas on how to fix this?


=VLOOKUP("*"&A1&"*",B1:G3,3,FALSE)


The format for all cells is text and if i search for any of the other field values by placing it in A1 it works. Thanks for your help in advanced.


#ABCDEFG
1SIPK0SIA133277108421.99.90LOS DEMÁS1Unidadescartucho para desionizador. Y .SIPK0SIA1 SimpliPak 1, Qty Pk 1 SIPK0SIA1
2=VLOOKUP("*"&A1&"*",B1:G3,3,FALSE)33277118421.99.90LOS DEMÁS1Unidadescartucho para desionizador. Y .SIMFILTER Simplicity Final Filter, Qty Pk 1 SIMFILTER
333277128421.99.90LOS DEMÁS1UnidadesFiltro para desionizador. Y .3501012210 Filtros UF, 10,000 lts para desionizador Ultra 373 3501012210
 
Perhaps use absolute ranges
Code: 
=VLOOKUP("*"&A1&"*",$B$1:$G$3,3,FALSE)
 
You are looking at a range that the first column contains nothing like the lookup text, so of course you get #N/A?

What exactly are you expecting, to look in column G? If so, what do you want it to return?
 
Bob Phillips said:
You are looking at a range that the first column contains nothing like the lookup text, so of course you get #N/A?

What exactly are you expecting, to look in column G? If so, what do you want it to return?
Click to expand...


Actually you're wrong the first column contains SIPK0SIA1 which is in column G not once but twice. cartucho para desionizador. Y .SIPK0SIA1 SimpliPak 1, Qty Pk 1 SIPK0SIA1. The correct answer is that because the value i want to return is to the left of the field that vlookup will find the answer in it will not work. Fixed with an index/match formula. Since vlookup only looks to the right it wouldn't return a value even if it found a match in that row. Thanks for trying to help anyways.
 
Last edited:
The first column in your lookup range is B not G, and that doesn't contain that text.
 
You must log in or register to reply here.
Back
Top