Help needed - VLOOKUP based on IF statement, or similar

[philobr]

Hi there,
I have a data set and Im trying to look up a specific value based on an initial look up in one column and then a secondary look up in another.

So data would look something like this:

A B C
Apples Granny Smyth 20
Apples Cooking 10
Apples Pink Lady 15


So based on the initial lookup of Apples, then a second lookup of Cooking to give me the total number.

Any help appreciated, thanks.

 

[steve the fish]

If you want a total sum or there is only one entry of each pair then:

=SUMIFS(C:C,A:A,"Apples",B:B,"Cooking")

Or if you just want the first hit:

=INDEX($C$1:$C$100,MATCH(1,INDEX(($A$1:$A$100="Apples")*($B$1:$B$100="Cooking"),0),0))

Or the last hit:

=LOOKUP(2,1/(($A$1:$A$100="Apples")*($B$1:$B$100="Cooking")),$C$1:$C$100)

 

[AlanY]

one way

Book1
	A	B	C
1	Apples	Granny Smyth	20
2	Apples	Cooking	10
3	Apples	Pink Lady	15
4
5
6	Apples	Cooking	10
Sheet14

Cell Formulas
Range	Formula
C6	=SUMPRODUCT(--(A1:A3=A6),--(B1:B3=B6),C1:C3)

 

[Special-K99]

=INDEX(C$1:C$3,MATCH(A6,IF(A$1:A$3=A$5,B$1:B$3),0),1)
Array formula, use Ctrl-Shift-Enter

Where A5 is Apples
and A6 is Cooking

 

[Fluff]

Something like

Excel 2013/2016
	A	B	C	D	E
2	Apples	Granny Smith	20		Apples
3	Apples	Cooking	10		Cooking
4	Apples	Bramley	15		10
Names

Cell Formulas
Range	Formula
E4	{=INDEX(C2:C4,MATCH(E2&"|"&E3,A2:A4&"|"&B2:B4,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[philobr]

Brilliant, thanks so much. Appreciate the help.