vlookup problem

[Glen Mettler]

Using Excel 2k.
Cell B3 = "10"
lookup formula = =VLOOKUP($B3,Events!$A$2:$B$12,2)
Problem is: anything after 09 comes back as "FM2" - I can't seem to get
"10" or "11" in the lookup.
What have I done wrong?


Sheet "Events" has:
Event Num Event Code
01 ATP
02 IBR
03 SRR
04 MDR
05 PSR
06 MRR
07 FRR
08 RRR
09 FM2
10 FM3
11 DCO

 

[Guest]

Did you create 01, 02, etc by Formatting the Cells as Text? By placing an
apostrophe at the beginning of the number? If so, your Lookup Table isn't in
Ascending Order, which is required for Closest Match Lookups.
The solution:
Put 10 and 11 at the beginning of the Table. Or, format all the numbers as
text. Or, use the Custom Number Format 00 to Format the cells.

tj

 

[Guest]

If you are using an exact match--if the number you look up always appears in
that first column, you can also skip what I wrote previouly and change your
formula like this:
=VLOOKUP($B3,Events!$A$2:$B$12,2,0)

Adding the 0 at the end forces Excel to Match the lookup value exactly a
cell in the Lookup Table. Therefore, if you looked up 0.25, youd get an error.

tj

 

[Glen Mettler]

All of values are text. When I add the ,0 to the vlookup function, I get
#N/A.
I have sorted, and formated but I can't seem to get past 09.

Glen

 

[Dave Peterson]

The usual culprits are text vs. numbers or leading/trailing spaces in either the
table or the value to lookup.

If you put =B3=events!A10
(both pointing at your cells that look like they contain 10, what do you get?)

Some more functions that may help you find the problem:

=isnumber(b3) (and events!a10)
=len(b3)
=istext(b3)

 

[Guest]

you have no error in what you have done, except that the data format of ur
main list might be wrong.
choose the column "event num" in event and choose format/cells , then in
numbar tab choose custom and enter the type "00" for the numbers. you may
have chosen text fromat in order to be able to show 01,02,...., correct it!