Formulat to lookup using 2 values

[Hall]

What formula construct can I use to lookup a value in another table using 2
values as my criteria? For example, my source table has

A B C
1 a b c
2 a bb cc
3 aa bbb ccc

I want to lookup up a value in column C where column A has "a" and column B
has "bb", giving me the value "cc".

How do I do that?

 

[Domenic]

Try...

=INDEX($C$1:$C$3,MATCH(1,($A$1:$A$3="a")*($B$1:$B$3="bb"),0))

or

=INDEX($C$1:$C$3,MATCH(1,($A$1:$A$3=E1)*($B$1:$B$3=F1),0))

....where E1 contains your first criteria, such as 'a', and F1 contains
your second criteria, such as 'bb. Both formulas need to be confirmed
with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Dave Peterson]

Saved from a previous post:

I like this syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.