VLOOKUP Frustration

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There are multiple fields (5) within my Excel table that depend upon lookup values. The VLOOKUP field upon which I am searching contains codes, similar to the following format

SU998
M77000
UU70801

In other words, no set string length, no rhyme or reason. I have set up the table and alphabetized the lookupfield. It works just FINE EXCEPT for the record ONE record that contains the entry, 90882 (It is the only code that does not start, nor does it contain, any letters) All cells were preformatted as text only fields. I get N/A errors for all "90882" records. WHAT DO I DO TO FIX this???
 
Hi
try formatting your lookup range as Text as well as the fields with the
search criteria. Probabla either of one is currently recognized as text
and the other one as number
 
JayceeJay

Make suure the text is exactly the same in your VLOOKUP table as in your
record!

The following two items are different, the second one has a space after the
number
and will return N/A error with VLOOKUP!

"90882"
"90882 "

HTH

George Gee


*JayceeJay* has posted this message:
 
Jayceejay,

Is it possible that 90882 is a number ?
You can check by entering the function ISNUMBER or ISTEXT in any empty cell
and refer to the cell containing 90882.
If you use ISTEXT and you get FALSE it's a number and vice versa.
Whatever it is : make sure that the value you use as lookup value in the
VLOOKUP function has the same attribute.
Apart from that : If it is text : make sure that there are no spaces before
or after the text itself.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

JayceeJay said:
There are multiple fields (5) within my Excel table that depend upon
Click to expand...
lookup values. The VLOOKUP field upon which I am searching contains codes,
similar to the following format:
SU9987
M77000t
UU708012

In other words, no set string length, no rhyme or reason. I have set up
Click to expand...
the table and alphabetized the lookupfield. It works just FINE EXCEPT for
the record ONE record that contains the entry, 90882 (It is the only code
that does not start, nor does it contain, any letters) All cells were
preformatted as text only fields. I get N/A errors for all "90882" records.
WHAT DO I DO TO FIX this????
 
You can force the lookup to look for text or numbers.
If the lookup table has numbers in the fields to lookup use =VLOOKUP(A1*1, .
.. . .
If the lookup table has text in the fields to lookup use
=VLOOKUP(TEXT(A1,"#"), . . . .
so regardless of how A1 is formatted it is converted to be the same as is
being looked up.
Barbara

JayceeJay said:
There are multiple fields (5) within my Excel table that depend upon
Click to expand...
lookup values. The VLOOKUP field upon which I am searching contains codes,
similar to the following format:
SU9987
M77000t
UU708012

In other words, no set string length, no rhyme or reason. I have set up
Click to expand...
the table and alphabetized the lookupfield. It works just FINE EXCEPT for
the record ONE record that contains the entry, 90882 (It is the only code
that does not start, nor does it contain, any letters) All cells were
preformatted as text only fields. I get N/A errors for all "90882" records.
WHAT DO I DO TO FIX this????
 
Thank you, ALL for your responses. I never DID figure out what the problem(s) were, but I deleted to wksheet containing the VLOOKUP table and started from SCRATCH. Everything worked out fine. Makes me wish I'd just done that in the first place....Oh, well...ya live & learn. Thanks again.
 
Back
Top