Vlookup with alphanumeric values
 

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 would expect via my Vlookup however when looking up FF*291 verses FF*1291 the 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 am using the exact match entry ie FALSE but am stuggling to understand this one. When I sort the codes and have FF*291 before FF*1291 it works. I want an exact match so not interested in having to sort my data. Any help is most welcome. Cheers

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

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

Hope that helps.

Thanks very much that worked a treat. Any how just on the same note and for my further understanding I had * in most of my codes but in particular I had a problem with the above as mentioned. When adding a 0 ie FF*0219 it worked also. Also when FF*219 was before FF*1219 in the column being looked up it worked. FF*2219 also worked correctly. I accept the * as a wild card entry but fail to understand why excel got stuck on this particular code. Does a Vlookup look down a column list when computing the Vlookup? It must be due to the above working when 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 bringing back the value from column B, the lookup will start at row 2, if it finds something that fits the criteria it will bring back that result and stop. If it doesn't, it carries on to row 3 then 4 then 5 and so on. So yes, it works its way down the list until it finds what you've asked for.

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 being looked at because it is only reading up to FF* and then finding the best match taking into account the wildcard? Cheers again

Oooooh, close but not quite!

It didn't stop reading when it found FF*. It stopped when it found the first instance of a value starting with FF and ending in 219, regardless of what was between the two.

So if the first value was FF_IWantToBuySpencerANiceColdPint_219, the formula would stop there, but if the first value was FF_IWantToBuySpencerANiceColdPint_219OrMaybeNot, the formula would skip over it as the wildcard doesn't cover the extra bit of nonsense at the end :)

Make sense?

ok mate all good now. Many thanks for your help. This website could of saved me hours of pain in the past! LOL.... Where is your nearest watering hole?

Not a problem my friend. I know what you mean about the website saving loads of time. I've been trawling through it for advice for many moons. Only recently decided to take the plunge and start giving back.

Nearest watering hole is anywhere on the Sussex coast. :)

The South Island in New Zealand is along way away from England!..Cheers again

Haha, it would be hard to get much further away!
No worries, I'll buy my own beer. Glad to help. :)