HELP! What function to use!!

[Guest]

Hi,
HELP!! important data for work!!

Trying to return a cell value when 2 conditions are met using 2 lookup values:

i.e.

A B C
x y 10
x z 20
b z 30


Im trying to return the value 20 when the lookup value I have is x and
another lookup value I have is Z....obviously Vlookup will only return the
first occurence of x when it comes to it and return the value 10.

....I have the values x and z on another sheet, but need excel somehow to use
both of these in front sheet to lookup and return the value 20....

basically i need it to say "When I see x AND z , I need to return the value
next to them....." and not "Theres x, lets return the value next to it", or
"theres z, lets return the value next to it

hope this makes sense...any help appreciated!

 

[Niek Otten]

Insert an extra column in your table in which you concatenate A and B. Concatenate your search argument as well.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
| HELP!! important data for work!!
|
| Trying to return a cell value when 2 conditions are met using 2 lookup values:
|
| i.e.
|
| A B C
| x y 10
| x z 20
| b z 30
|
|
| Im trying to return the value 20 when the lookup value I have is x and
| another lookup value I have is Z....obviously Vlookup will only return the
| first occurence of x when it comes to it and return the value 10.
|
| ...I have the values x and z on another sheet, but need excel somehow to use
| both of these in front sheet to lookup and return the value 20....
|
| basically i need it to say "When I see x AND z , I need to return the value
| next to them....." and not "Theres x, lets return the value next to it", or
| "theres z, lets return the value next to it
|
| hope this makes sense...any help appreciated!
|
|

 

[Guest]

=SUMPRODUCT(-(A1:100="x"),-(B1:B100="z"),C1:C100)