VLOOKUP using text

  • Thread starter Thread starter Ken Donald
  • Start date Start date
K

Ken Donald

I have done a spreadsheet on medical terms and wish to
use the VLOOKUP function to return the medical term when
a Lay term is entered. When I do so, I get a return of
the wrong cell (one cell up). When using numbers in place
of text, the return is correct. What am I doing wrong?
 
Ken,

It is probably because the items are not sorted in the lookup table. Add
,FALSE to the end of the lookup formula, such as

=VLOOKUP(val,lookup_table,2,FALSE)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Ken Donald said:
I have done a spreadsheet on medical terms and wish to
use the VLOOKUP function to return the medical term when
a Lay term is entered. When I do so, I get a return of
the wrong cell (one cell up). When using numbers in place
of text, the return is correct. What am I doing wrong?
Click to expand...

Always post the formula you are using!

VLOOKUP can have four parameters, although the fourth is optional:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

I suspect that you are probably doing an approximate lookup; that is, the
fourth parameter (range_lookup) is either omitted or TRUE. In this case
(assuming that the lookup table is sorted), if it cannot find an exact match
it will return the next lowest, i.e. "one cell up". To test this, make
range_lookup FALSE. No match will then result in #N/A.

Note that matching text requires that the text is exactly the same; an extra
space character, for example, causes it not to match.
 
Back
Top