Lookup by multiple criteria?

[muster]

I have a table like the following and I need to look up the 3rd column
based on the first 2. Anybody has a simple solution? Thanks a lot!

A a 3
b 5
c 1
B b 2
c 4

 

[Guest]

Assuming your source data is structured as below in cols A to C, from row1 down

A a 3
A b 5
A c 1
B b 2
B c 4
B d 9
etc

Just do a manual one-time fill from above for col A, to fully populate it.
If you have a lot of these to do, see Debra's page at:
http://www.contextures.com/xlDataEntry02.html
for some techniques

Then assuming inputs made in E1: A, in F1: c
Place in G1, array-enter (press CTRL+SHIFT+ENTER):
=INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=E1)*($B$1:$B$100=F1),0))
would return the required multiple criteria lookup result from col C, viz: 1

Copy G1 down to return correspondingly for other input pairs in E2:F2,
E3:F3, etc
Adjust the ranges to suit

 

[T. Valko]

Assume this table is in the range A2:C6

A.....a.....3
.........b....5
.........c....1
B.....b....2
........c.....4

F2 = lookup value = A
G2 = lookup value = c

=INDEX(C2:C6,MATCH(F2,A2:A6,0)-1+MATCH(G2,B6:OFFSET(A2,MATCH(F2,A2:A6,0)-1,1)))

Result = 1

Biff

 

[Lori]

Another alternative with data as above:

=LOOKUP(2,1/(B2:B6=G2)/(LOOKUP(ROW(2:6),ROW(2:6)/(A2:A6<>""),A2:A6)
=F2),C2:C6)


[Note: The LOOKUP(ROW(2:6),ROW(2:6)/(A2:A6<>""),A2:A6) part fills in
the blanks in the first column and the other part finds the last match
in the range]

 

[T. Valko]

Slight tweak needed in the 2nd MATCH: (need to set the match_type to 0):

=INDEX(C2:C6,MATCH(F2,A2:A6,0)-1+MATCH(G2,B6:OFFSET(A2,MATCH(F2,A2:A6,0)-1,1),0))

Biff