Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
#2
|
|||
|
|||
Quote:
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. |
#3
|
|||
|
|||
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
|
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Quote:
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? |
#7
|
|||
|
|||
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?
|
#8
|
|||
|
|||
Quote:
Nearest watering hole is anywhere on the Sussex coast. :) |
#9
|
|||
|
|||
The South Island in New Zealand is along way away from England!..Cheers again
|
#10
|
|||
|
|||
Quote:
No worries, I'll buy my own beer. Glad to help. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup with numeric and alphanumeric identifiers in lookup_value | Excel Discussion (Misc queries) | |||
Removing Text from Alphanumeric values | Excel Discussion (Misc queries) | |||
VLOOKUP with numeric and alphanumeric values | Excel Discussion (Misc queries) | |||
how do i enter alphanumeric values? | Excel Discussion (Misc queries) | |||
Max-function with alphanumeric values?!? | Excel Programming |