Locate row based on two criteria

[Guest]

I have a list of data where column A and Column B together identify the row
uniquely.

From a formula I want to locate a specific row matching the TWO criteria
(i.e. both value in A and B columna should match my input values). I want to
return the value in column C.

ex: (list is sorted - A asc, then B asc)
A- Item B-Item location C-Stock Qty
Barbie doll X 10
Barbie doll Y 6
Barbie doll Z 3
Spiderman X 2
Spiderman Y 4

I want to find how many barbie dolls are on location Y (=6) and use this
value for further processing.

Should be simple, but I'm not very advanced in Excel. With only one criteria
I would have used VLOOKUP......

Grateful for any help on this....

 

[Sandy Mann]

Try:

=SUMPRODUCT((A2:A6="Barbie doll")*(B2:B6="Y")*C2:C6)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

The function always returns zero since the entries are not numeric. I want to
return the exact value of column C, and I'm not sure I understand how
sumproduct can do that.. Or am I missing something ?
Kathrine

 

[Sandy Mann]

I get 6 whether column C has text number or real numbers. The other colums
do not need to be numbers for SUMPRODUCT() to work. See

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

for an explanation of this use of SUMPRODUCT()

If you want you can send me a sample of your spreadsheet and I will have a
look to see whay it is not working for you.

Correct my address as it says in my signature by replacing the
@maininator.com which is a spam trap.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

You are right. It works! I was using the function wizard. It puts ; in
between the arguments. Replacing with * as in your example did it!

Thank you!

 

[Sandy Mann]

Hi Katherine,

I'm glad you got it sorted out. Using SUMPRODUCT() this way was never
envisaged by Microsoft, it was some of the clever people around here that
extended its use.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk