Range Lookup? Is there such a thing like this?
 

Greetings!

My latest problem goes like this: :)

In Columns A,B, &C, I have a product ID, a date, and amount ordered. A1 B1
and C1 look like this:

15016 1/14/2005 70


I am trying to see if those three cells appear anywhere in a lookup table in
columns D, E, &F. The range, lets say is (D1:F5) and looks like this:

15016 1/10/2005 70
15022 1/15/2005 70
15016 1/14/2005 35
15035 1/14/2005 90
15016 1/14/2005 50

I was hoping to create a formula in an adjacent cell that would indicate yes
or no.

The answer to the above scenario would be "no" or "false" because nowhere
did anyone order 70 units of Product #15016 on 1/14/2005

Thank you SO much.

Michael Saffer

=IF(SUMPRODUCT(--($D$1:$D$5&$E$1:$E$5&$F$1:$F$5=A1&B1&C1))0,"yes", "no")
on any cell within that sheet to test if all values on a1,b1 and c1 exist in
the array

"Michael Saffer" wrote in message
...
Greetings!

My latest problem goes like this: :)

In Columns A,B, &C, I have a product ID, a date, and amount ordered. A1
B1
and C1 look like this:

15016 1/14/2005 70


I am trying to see if those three cells appear anywhere in a lookup table
in
columns D, E, &F. The range, lets say is (D1:F5) and looks like this:

15016 1/10/2005 70
15022 1/15/2005 70
15016 1/14/2005 35
15035 1/14/2005 90
15016 1/14/2005 50

I was hoping to create a formula in an adjacent cell that would indicate
yes
or no.

The answer to the above scenario would be "no" or "false" because nowhere
did anyone order 70 units of Product #15016 on 1/14/2005

Thank you SO much.

Michael Saffer

Thank you N Harkawat! Worked perfectly!
Regards, Michael Saffer



"N Harkawat" wrote in message
...
=IF(SUMPRODUCT(--($D$1:$D$5&$E$1:$E$5&$F$1:$F$5=A1&B1&C1))0,"yes", "no")
on any cell within that sheet to test if all values on a1,b1 and c1 exist
in
the array

"Michael Saffer" wrote in message
...
Greetings!

My latest problem goes like this: :)

In Columns A,B, &C, I have a product ID, a date, and amount ordered. A1
B1
and C1 look like this:

15016 1/14/2005 70


I am trying to see if those three cells appear anywhere in a lookup
table
in
columns D, E, &F. The range, lets say is (D1:F5) and looks like this:

15016 1/10/2005 70
15022 1/15/2005 70
15016 1/14/2005 35
15035 1/14/2005 90
15016 1/14/2005 50

I was hoping to create a formula in an adjacent cell that would indicate
yes
or no.

The answer to the above scenario would be "no" or "false" because
nowhere
did anyone order 70 units of Product #15016 on 1/14/2005

Thank you SO much.

Michael Saffer