Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
lookup
Hi All,
In a workbook, I have 2 sheets ( sheet1 & sheet2) Sheet1 – A B C D E 1 Codes Date Grade Action Status 2 1025 20-01-2008 A N, S 3 2785 20-04-2008 B S Sheet2 – A B C D 1 Codes Date Grade Status 2 1025 20-01-2008 A Waiting 3 1035 25-01-2008 B Approved 4 1038 26-03-2008 A Rejected 5 2035 15-02-2008 C Approved 6 2038 10-01-2007 A Approved 7 2087 15-02-2008 A Approved 8 2785 20-04-2008 B Rejected 9 3596 21-04-2006 C Rejected . . Sheet 1 has some data which is matching sheet 2. What I need is sheet 1 column E status from Sheet 2. Firstly, In sheet 1 – if the the Col D Action is N then only I need status. In the above case row 2 has action as N. So, match codes, date and grade (all 3). If it matches with sheet 2 then give me the status in col E (sheet1). Thanks in advance. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
lookup
=IF(ISNUMBER(FIND("N",D2)),IF(ISNUMBER(MATCH(1,(Sh eet2!A2:A200=A2)*)(Sheet2!B2:B200=B2)*(Sheet2!C2:C 200=C2),0)),INDEX(Sheet2!D2:D200,MATCH(1,(Sheet2!A 2:A200=A2)*)(Sheet2!B2:B200=B2)*(Sheet2!C2:C200=C2 ),0)),""),"")
as an array formula, commit with Ctrl-Shift-Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "muddan madhu" wrote in message ... Hi All, In a workbook, I have 2 sheets ( sheet1 & sheet2) Sheet1 – A B C D E 1 Codes Date Grade Action Status 2 1025 20-01-2008 A N, S 3 2785 20-04-2008 B S Sheet2 – A B C D 1 Codes Date Grade Status 2 1025 20-01-2008 A Waiting 3 1035 25-01-2008 B Approved 4 1038 26-03-2008 A Rejected 5 2035 15-02-2008 C Approved 6 2038 10-01-2007 A Approved 7 2087 15-02-2008 A Approved 8 2785 20-04-2008 B Rejected 9 3596 21-04-2006 C Rejected .. .. Sheet 1 has some data which is matching sheet 2. What I need is sheet 1 column E status from Sheet 2. Firstly, In sheet 1 – if the the Col D Action is N then only I need status. In the above case row 2 has action as N. So, match codes, date and grade (all 3). If it matches with sheet 2 then give me the status in col E (sheet1). Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |