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


"Julieeeee" wrote in message
...
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,

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Julie,

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

HTH,
Bernie
MS Excel MVP


"Julieeeee" wrote in message
...
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

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

"Bernie Deitrick" wrote:

Julie,

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

HTH,
Bernie
MS Excel MVP


"Julieeeee" wrote in message
...
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,

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" wrote in message
...
How? It tells me "too many parameters" if I add a ,FALSE at then end

"Bernie Deitrick" wrote:

Julie,

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

HTH,
Bernie
MS Excel MVP


"Julieeeee" wrote in message
...
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

Julieeeee Wrote:
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.


--
rookerr
------------------------------------------------------------------------
rookerr's Profile: http://www.excelforum.com/member.php...o&userid=26588
View this thread: http://www.excelforum.com/showthread...hreadid=398602

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

"Bernie Deitrick" wrote:

Julie,

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

HTH,
Bernie
MS Excel MVP


"Julieeeee" wrote in message
...
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,

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" wrote in message
...
How? It tells me "too many parameters" when I add ,FALSE at the end.

"Bernie Deitrick" wrote:

Julie,

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

HTH,
Bernie
MS Excel MVP


"Julieeeee" wrote in message
...
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

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

"Bernie Deitrick" wrote:

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" wrote in message
...
How? It tells me "too many parameters" if I add a ,FALSE at then end

"Bernie Deitrick" wrote:

Julie,

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

HTH,
Bernie
MS Excel MVP


"Julieeeee" wrote in message
...
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