=VLOOKUP("a",table1,2,FALSE) is not working for me why?

  • Thread starter Thread starter J
  • Start date Start date
J

J

WindowsXP, and Excell 2002 (XP), Celeron 1gig

Something is wrong here.
I have rebooted. Twice

And rebuilt the notebook.


Cell formula
result
------------------------------------------ -------------
=VLOOKUP("a",table1,2,FALSE) a
=VLOOKUP("A",table1,2,FALSE) a

=VLOOKUP("a",table1,2,TRUE) A
=VLOOKUP("A",table1,2,TRUE) A


-----------table1------------
a a

A A
b b
B B


The FALSE parameter is supposed to display the actual value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match. If TRUE or omitted, an
approximate match is returned. In other words, if an exact match is not
found, the next largest value that is less than lookup_value is returned. If
FALSE, VLOOKUP will find an exact match. If one is not found, the error
value #N/A is returned.


Anybody have an idea?

J
 
Not sure if I'm reading this right, but see if this helps:-

The way VLOOKUP works is that if, as you have done, you define a table with a
name eg MyTable and that table refers to the range say A1:B100, then the formula

=VLOOKUP("a",MyTable,2,FALSE) FALSE can also be written as 0, eg:-
=VLOOKUP("a",MyTable,2,0)

will go to the range MyTable (A1:B100), and look in the leftmost column of that
Table to try and find the value "a", (if you have a 0 or FALSE at the end it
will look for an exact match only!!), and if it finds it, it will return the
value from the 2nd column (Because of the 2 in the formula).

If it doesn't find an exact match then it will return #N/A which means it
doesn't exist. It may appear to that the value is there, but a leading or
trailing space may make the data look identical, but will in fact mena that it
is different, eg:-

John
John

These two are NOT the same. The second John has 2 spaces after it, but visually
you could never tell that.

If you had had 5 columns in your table, so that the range MyTable referred to
was say A1:E100, then the 2 in the formula could be changed to 3 or 4 or 5, and
the formula, if it indeeds finds a match will then return the corresponding
value from Col C, D or E depending on that number.

If you omit the last optional argument of FALSE or 0, then Excel will look for
the nearest match to the value you have asked it to look up (See Help for more
detail), and this is often used when looking a table of values for say a
discount or commission structure, eg:-

A B
10 10%
50 20%
100 30%
150 40%
200 50%
250 60%

=VLOOKUP(65,MyTable,2) assuming MyTable was A1:B100 would return 20% as it
would 'find' 50 as being the largest number smaller than your target number, and
then give you the corresponding value from the 2nd Column of MyTable.

If you don't use the last argument of FALSE or 0 then the data in the first
column of your table MUST be sorted, but with the last arg of 0 or FALSE it does
not need to be.

Hopefully this helps, but if not then I'll happily send you an example workbook
with a working formula so you can test it.
 
When you have sorted out the space etc as Ken suggested it looks as thought
you want to distinguish between upper and lower case in your look up.
Lookups are not case sensitive, but you can apply a work round. Here is one
posted earlier by Niek Otten which I made a note of.--



Insert a new column in which you add something to the 3-letter key if it is
uppercase, like:

=A1&IF(CODE(RIGHT(A1,1))<96,"A","")

Do something similar for your lookup-key and then apply VLOOKUP to these new
items
 
With the example you've given, you could use CODE instead of VLOOKUP, to
return the values, e.g.
=CODE("a")
would return 97
 
Hi Jessie,

I think you have just worked out that VLOOKUP is NOT case sensitive.

Can you use an alternative to 'a' if you have already used 'A'?


David


ps VBA *IS* case sensitive.

Explore and Enjoy
 
Back
Top