lookup function bringing in wrong info

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

Guest

Sheet1 has a list of confirmations but no reservation#. Sheet2 has some of
the same info, but not all confirmations have res#s. I want to see if the
conf# in sheet1 has a corresponding res# in sheet2. The formulas I am using
is>
=LOOKUP(P:P,sheet2!O:O,sheet2!F:F)
where the conf# are in column P in sheet1 and in column O on sheet2, and the
res# is in column F on sheet2.
The results are giving me res# that don't correspond to the correct conf#.
Since not all conf#s have res#s, there should ne "N/A"s but there aren't any.
What am I doing wrong??
Thanks!
Julie
 
Julie,

Use False as the fourth parameter in your LOOKUP function, to force an exact match.

HTH,
Bernie
MS Excel MVP
 
Julie,

Sorry, I was thinking of VLOOKUP and HLOOKUP.

Instead of LOOKUP, you need to use a combination of MATCH and INDEX:

=INDEX(Sheet2!F:F,MATCH(P:P,Sheet2!O:O,FALSE))

HTH,
Bernie
MS Excel MVP
 
Julie,

Sorry, I was thinking of VLOOKUP and HLOOKUP.

Instead of LOOKUP, you need to use a combination of MATCH and INDEX:

=INDEX(Sheet2!F:F,MATCH(P:P,Sheet2!O:O,FALSE))

HTH,
Bernie
MS Excel MVP
 
Julieeeee said:
Sheet1 has a list of confirmations but no reservation#. Sheet2 has some
of
the same info, but not all confirmations have res#s. I want to see if
the
conf# in sheet1 has a corresponding res# in sheet2. The formulas I am
using
is>
=LOOKUP(P:P,sheet2!O:O,sheet2!F:F)
where the conf# are in column P in sheet1 and in column O on sheet2,
and the
res# is in column F on sheet2.
The results are giving me res# that don't correspond to the correct
conf#.
Since not all conf#s have res#s, there should ne "N/A"s but there
aren't any.
What am I doing wrong??
Thanks!
Julie
Click to expand...

I would have used the syntax vlookup(the value to look up, the table
range, the column number whose value you wish to have returned,
followed by "false" to give you an EXACT match. Without the FALSE
statement, your lookup will look up your value to look up but if there
is NOT an exact match, then the lookup will go to the previous value
and give you eroneous results like you seem to be getting.
 
Julie,

Sorry, I was thinking of VLOOKUP and HLOOKUP.

Instead of LOOKUP, you need to use a combination of MATCH and INDEX:

=INDEX(Sheet2!F:F,MATCH(P:P,Sheet2!O:O,FALSE))

HTH,
Bernie
MS Excel MVP
 
That works!!! Thank you so much. That has been bugging me for a long time!!
i am so glad I asked.
 
Back
Top