HELP with Looking Up 2 Condition

[mova]

I am having difficulty creating a function that will lookup 2 values and return another.

For example:
Apple Red Delicious 1
Macinotsh 2
Granny Smith 3

want to match with:
Apple, Macintosh and retun the value 2

I want to create a lookup funtion that looks up the first column, then the 2nd column and if both match then return the value in the 3rd column

Please help!

 

[barry houdini]

Try

=INDEX(C1:C10,MATCH(1,(A1:A10="Apple")*(B1:B10="Macintosh"),0))

confirmed with CTRL+SHIFT+ENTER

You can replace text, e.g. "Apple" with cell refs if you so wish, e.g.

=INDEX(C1:C10,MATCH(1,(A1:A10=H1)*(B1:B10=H2),0))

where H1 contains "Apple" and H2 "macintosh"

 

[Aladin Akyurek]

I am having difficulty creating a function that will lookup 2 values and return another.

For example:
Apple Red Delicious 1
Macinotsh 2
Granny Smith 3

want to match with:
Apple, Macintosh and retun the value 2

I want to create a lookup funtion that looks up the first column, then the 2nd column and if both match then return the value in the 3rd column

Please help!

Book4
	A	B	C	D	E	F	G	H
1	X	Y	Z	X#Y
2	Apple	Red Delicious	1	Apple#Red Delicious		Apple	Macintosh	2
3		Macintosh	2	Apple#Macintosh
4		Granny Smith	3	Apple#Granny Smith
Sheet1

D2, copied down:

=IF(A2<>"",A2,LOOKUP(REPT("z",255),$A$1:A1))&"#"&B2

H2:

=INDEX($C$2:$C$4,MATCH(F2&"#"&G2,$D$2:$D$4,0))

 

[big_mac]

I just like counting apples too.

Book4
	A	B	C	D	E	F	G
2	apples	RedDelicious	1	apples
3	apples	Macinotsh	2	Macinotsh	2
4	apples	GrannySmith	3
5	apples	Macinotsh		RedDelicious	3
6	apples	RedDelicious	1
7	apples	Macinotsh		GrannySmith	8
8	apples	RedDelicious	1
9	apples	GrannySmith	5
10
11
12
Sheet1

 

[mova]

Barry, I tried yours and I am still coming up with N#A
I think the error is in the match part of the function, but I am not sure
It could be that my actual data is much more complicated than apples.

 

[barry houdini]

Have you confirmed with CTRL+SHIFT+ENTER?

curly braces should appear around formula in formula bar

Book1
	A	B	C	D	E	F	G
1
2						apple
3	apple	anorak	1			macintosh
4	banana	macintosh	2			4
5	grape	macintosh	3
6	apple	macintosh	4
7
Sheet3

 

[mova]

I dont know what it means to confirm with CTL+SHIFT+ENTER

 

[mova]

Aladin - I tried the formula, and it seems to work for my top entry but when i copy the formula down it is returning the same value. I am not sure why!

 

[barry houdini]

I dont know what it means to confirm with CTL+SHIFT+ENTER

If you select the cell with the formula then....

Press F2

Hold down CTRL and SHIFT keys together and whilst doing so press ENTER

curly braces should appear around the formula as per my example

 

[mova]

i did that but nothing changed - i see the brakets now but the result is still N/A
what is it supposed to do?

Thanks for all your help!