Lookup by multiple criteria?
 

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

Lookup by multiple criteria?
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"muster" wrote:
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

Lookup by multiple criteria?
 

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

"muster" wrote in message
ps.com...
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

Lookup by multiple criteria?
 

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]

On 12 Mar, 21:59, "muster" wrote:
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

Lookup by multiple criteria?
 

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

"T. Valko" wrote in message
...
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

"muster" wrote in message
ps.com...
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