lookup function bringing in wrong info

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
 
B

Bernie Deitrick

Julie,

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

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

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
 
B

Bernie Deitrick

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
 
R

rookerr

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

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.
 
B

Bernie Deitrick

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
 
G

Guest

That works!!! Thank you so much. That has been bugging me for a long time!!
i am so glad I asked.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top