lookup function bringing in wrong info

[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,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

 

[Bernie Deitrick]

Julie,

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

HTH,
Bernie
MS Excel MVP

 

[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,Sheet2!O:O,FALSE))

HTH,
Bernie
MS Excel MVP

 

[Guest]

How? It tells me "too many parameters" if I add a ,FALSE at then end

 

[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,Sheet2!O:O,FALSE))

HTH,
Bernie
MS Excel MVP

 

[rookerr]

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

 

[Guest]

How? It tells me "too many parameters" when I add ,FALSE at the end.

 

[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,Sheet2!O:O,FALSE))

HTH,
Bernie
MS Excel MVP

 

[Guest]

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