HELP with Looking Up 2 Condition

mova

New Member
Joined
Aug 25, 2006
Messages
14
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!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCDEFGH
1XYZX#Y
2AppleRed Delicious1Apple#Red DeliciousAppleMacintosh2
3Macintosh2Apple#Macintosh
4Granny Smith3Apple#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

Board Regular
Joined
Jul 21, 2006
Messages
167
I just like counting apples too.
Book4
ABCDEFG
2applesRedDelicious1apples
3applesMacinotsh2Macinotsh2
4applesGrannySmith3
5applesMacinotshRedDelicious3
6applesRedDelicious1
7applesMacinotshGrannySmith8
8applesRedDelicious1
9applesGrannySmith5
10
11
12
Sheet1
 

mova

New Member
Joined
Aug 25, 2006
Messages
14

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Have you confirmed with CTRL+SHIFT+ENTER?

curly braces should appear around formula in formula bar
Book1
ABCDEFG
1
2apple
3appleanorak1macintosh
4bananamacintosh24
5grapemacintosh3
6applemacintosh4
7
Sheet3
 

mova

New Member
Joined
Aug 25, 2006
Messages
14

ADVERTISEMENT

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

mova

New Member
Joined
Aug 25, 2006
Messages
14
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Aug 25, 2006
Messages
14
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!
 

Forum statistics

Threads
1,136,512
Messages
5,676,289
Members
419,618
Latest member
Grisego

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top