Excel 2007 LOOKUP Problem?
 

I have been using the LOOKUP function for a long time. It worked with very
little problems in Excel 2003. I am having a problem in Excel 2007. For
some reason it will not give me an exact match. It will only return a value
that is approximate. I have tried using VLOOKUP and INDEX with MATCH. Both
gave me N/A errors. Does anyone have any suggestions on how to get an exact
match using LOOKUP in Excel 2007? Thank you.

Excel 2007 LOOKUP Problem?
 

exact match
VLOOKUP and INDEX with MATCH. Both
gave me N/A errors.

Did you specify in the formulas that you wanted an exact match?

=VLOOKUP(A1,D1:E10,2,0)
=INDEX(E1:E10,MATCH(A1,D1:D10,0))

The 0 in each formula means you want an exact match.

With LOOKUP, you can't specify that you want an exact match. If an exact
match isn't found it returns an approx match by default. Also, using LOOKUP,
the lookup_vector *must* be sorted in ascending order.


--
Biff
Microsoft Excel MVP


"robertbjr" wrote in message
...
I have been using the LOOKUP function for a long time. It worked with very
little problems in Excel 2003. I am having a problem in Excel 2007. For
some reason it will not give me an exact match. It will only return a
value
that is approximate. I have tried using VLOOKUP and INDEX with MATCH.
Both
gave me N/A errors. Does anyone have any suggestions on how to get an
exact
match using LOOKUP in Excel 2007? Thank you.

Excel 2007 LOOKUP Problem?
 

LOOKUP does not look for an exact match. VLOOKUP, HLOOKUP and MATCH will
look for an exact match. These functions have to my knowledge always worked
this way. In other words, this is not unique to Excel 2007. The functions
work as they did in previous versions.

Tyro

"robertbjr" wrote in message
...
I have been using the LOOKUP function for a long time. It worked with very
little problems in Excel 2003. I am having a problem in Excel 2007. For
some reason it will not give me an exact match. It will only return a
value
that is approximate. I have tried using VLOOKUP and INDEX with MATCH.
Both
gave me N/A errors. Does anyone have any suggestions on how to get an
exact
match using LOOKUP in Excel 2007? Thank you.

Excel 2007 LOOKUP Problem?
 

Clarification:

The 0 in each formula means you want an exact match.

The 0 in each formula means *the data is not sorted* therefore your
intention is to find an exact match.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
exact match
VLOOKUP and INDEX with MATCH. Both
gave me N/A errors.

Did you specify in the formulas that you wanted an exact match?

=VLOOKUP(A1,D1:E10,2,0)
=INDEX(E1:E10,MATCH(A1,D1:D10,0))

The 0 in each formula means you want an exact match.

With LOOKUP, you can't specify that you want an exact match. If an exact
match isn't found it returns an approx match by default. Also, using
LOOKUP, the lookup_vector *must* be sorted in ascending order.


--
Biff
Microsoft Excel MVP


"robertbjr" wrote in message
...
I have been using the LOOKUP function for a long time. It worked with
very
little problems in Excel 2003. I am having a problem in Excel 2007. For
some reason it will not give me an exact match. It will only return a
value
that is approximate. I have tried using VLOOKUP and INDEX with MATCH.
Both
gave me N/A errors. Does anyone have any suggestions on how to get an
exact
match using LOOKUP in Excel 2007? Thank you.