Vlookup with alphanumeric values

[Hogan]

Hi, first time posting for me. Great site which has helped be out alot
Anyhow using a Vlookup for stock codes and have come across a problem
In the many stock codes I am looking up the majority return what I woul
expect via my Vlookup however when looking up FF*291 verses FF*1291 th
vlookup always returns the FF*1291 result and not the result for FF*291
So my result is the same for both stock codes which are different! I a
using the exact match entry ie FALSE but am stuggling to understand thi
one. When I sort the codes and have FF*291 before FF*1291 it works.
want an exact match so not interested in having to sort my data. An
help is most welcome. Cheer

 

[Spencer101]

Hi, first time posting for me. Great site which has helped be out alot
Anyhow using a Vlookup for stock codes and have come across a problem
In the many stock codes I am looking up the majority return what I woul
expect via my Vlookup however when looking up FF*291 verses FF*1291 th
vlookup always returns the FF*1291 result and not the result for FF*291
So my result is the same for both stock codes which are different! I a
using the exact match entry ie FALSE but am stuggling to understand thi
one. When I sort the codes and have FF*291 before FF*1291 it works.
want an exact match so not interested in having to sort my data. An
help is most welcome. Cheers

Excel looks at an * as a wildcard. This wildcard specifically coul
represent any number of characters, so could be no characters or coul
be 30 characters (or any other number). Excel will perform the VLOOKU
and assume that you mean the * as a wildcard and therefore is doin
exactly what you've asked of it.

To make this work properly you would have to replace all the *'s wit
another character such as a hyphen or an underscore.

Hope that helps

 

[Hogan]

Thanks very much that worked a treat. Any how just on the same note an
for my further understanding I had * in most of my codes but i
particular I had a problem with the above as mentioned. When adding a
ie FF*0219 it worked also. Also when FF*219 was before FF*1219 in th
column being looked up it worked. FF*2219 also worked correctly.
accept the * as a wild card entry but fail to understand why excel go
stuck on this particular code. Does a Vlookup look down a column lis
when computing the Vlookup? It must be due to the above working whe
FF*219 was before FF*1219 in my list. Appreciate your help. Cheer

 

[Spencer101]

Thanks very much that worked a treat. Any how just on the same note an
for my further understanding I had * in most of my codes but i
particular I had a problem with the above as mentioned. When adding a
ie FF*0219 it worked also. Also when FF*219 was before FF*1219 in th
column being looked up it worked. FF*2219 also worked correctly.
accept the * as a wild card entry but fail to understand why excel go
stuck on this particular code. Does a Vlookup look down a column lis
when computing the Vlookup? It must be due to the above working whe
FF*219 was before FF*1219 in my list. Appreciate your help. Cheers

If your VLOOKUP range was, for example, A2 : B10 and you're bringin
back the value from column B, the lookup will start at row 2, if i
finds something that fits the criteria it will bring back that resul
and stop. If it doesn't, it carries on to row 3 then 4 then 5 and s
on. So yes, it works its way down the list until it finds what you'v
asked for

 

[Hogan]

Thanks again. I thought as much. So one last question then FF*1219
is deemed to be the same as FF*219 when above FF*219 in the column bein
looked at because it is only reading up to FF* and then finding the bes
match taking into account the wildcard? Cheers agai