Lookup by multiple criteria.

M

MFM

I need to look up dept by date and emp #. Please see c15. Thanks

A B C
1 Date Emp # Dept
2 5/1/2007 A1054 Sales
3 6/1/2007 B1074 Prod
4 7/1/2007 C2074 Fin
5 8/1/2007 D1085 Sales
6 9/1/2007 e7805 Fin
7 10/2/2007 f8091 Prod
8 11/2/2007 g9071 Sales
9 12/3/2007 g7081 Prod
10 1/3/2008 h9081 Fin
11 2/3/2008 i70550 Sales
12 3/5/2008 j75520 Sales
13
14 Date Emp # DEPT
15 5/1/2007 A1054 =if(vlookup(A15,a2:c12,3)=b15,c,?
16 6/1/2007 B1074
17 7/1/2007 C2074
18 8/1/2007 D1085
19 9/1/2007 e7805
20 10/2/2007 f8091
21 11/2/2007 g9071
22 12/3/2007 g7081
23 1/3/2008 h9081
24 2/3/2008 i70550
25 3/5/2008 j75520
 
M

Mike H

Try this in C15

=INDEX(C2:C12,MATCH(A15&B15,A2:A12&B2:B12,0))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike
 
G

Gary''s Student

First use SUMPRODUCT to get the row and then use OFFSET to retrieve the value:

=OFFSET(C1,SUMPRODUCT((A1:A12=DATEVALUE("5/1/2007"))*(B1:B12="A1054")*ROW(A1:A12))-1,0)
 
J

John C

You could insert a helper column between B & C. This would move Dept to
column D, and in the new C column:
C2: =A2&B2 .... copy down as needed.

Then, your formula would be in D15 now:
D15: =IF(ISNA(VLOOKUP(A15&B15,C2:D12,2,FALSE)),"no
match",VLOOKUP(A15&B15,C2:D12,2,FALSE))
 
M

MFM

Thanks for your help, it worked fine.

Mike H said:
Try this in C15

=INDEX(C2:C12,MATCH(A15&B15,A2:A12&B2:B12,0))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top