| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup by multiple criteria.
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup by multiple criteria.
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 "MFM" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup by multiple criteria.
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) -- Gary''s Student - gsnu200814 "MFM" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup by multiple criteria.
=INDEX($C$2:$C$12,MATCH(1,INDEX(($A$2:$A$12=A15)*( $B$2:$B$12=B15),),))
"MFM" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup by multiple criteria.
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)) -- ** John C ** "MFM" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup by multiple criteria.
Thanks for your help, it worked fine.
"Mike H" wrote: 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 "MFM" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
| Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
| Lookup on multiple criteria and max | Excel Worksheet Functions | |||
| Lookup with multiple criteria... please help! | Excel Worksheet Functions | |||
| LookUp with multiple criteria | Excel Worksheet Functions | |||
Linear Mode
