lookup

[muddan madhu]

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.

 

[Bob Phillips]

=IF(ISNUMBER(FIND("N",D2)),IF(ISNUMBER(MATCH(1,(Sheet2!A2:A200=A2)*)(Sheet2!B2:B200=B2)*(Sheet2!C2:C200=C2),0)),INDEX(Sheet2!D2200,MATCH(1,(Sheet2!A2: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)



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.