I have 2 separate worksheets with similar information. My Plant column in
worksheet 1 has some empty cells. I want to return a value for Plant from my
2nd worksheet where 2 values in a row from the 1st worksheet matches 2 values
in 2 columns of the 2nd worksheet. Example:

worksheet 1
a b c d e
1 code item# name SUBC plant
2 a12 1001 s1f 1 aok
3 a12 1002 s1f2 k bal
4 a12 1003 tex 1 bal
5 a14 1004 saf s aok
6 a14 1005 plc 1
7 a15 1006 wak 1 bal

worksheet 2
a b c d e
1 code item# name SUBC plant
2 a12 1001 s1f 1 bal
3 a12 1007 win k aok
4 a14 1005 plc 1 aok


For cell e6 in worksheet 1, I want to return the value of column e in
worsheet 2 where,

1. b6 in wksht1 = value from b:b in wksht 2 and
2. d6 in wksht1 = value from d:d in wksht2

I hope this makes sense. Any ideas?

Try this array formula** :

=INDEX(Sheet2!E2:E4,MATCH(1,IF(Sheet2!B2:B4=B6,IF( Sheet2!D2:D4=D6,1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"brownmre" wrote in message
...
I have 2 separate worksheets with similar information. My Plant column in
worksheet 1 has some empty cells. I want to return a value for Plant from
my
2nd worksheet where 2 values in a row from the 1st worksheet matches 2
values
in 2 columns of the 2nd worksheet. Example:

worksheet 1
a b c d e
1 code item# name SUBC plant
2 a12 1001 s1f 1 aok
3 a12 1002 s1f2 k bal
4 a12 1003 tex 1 bal
5 a14 1004 saf s aok
6 a14 1005 plc 1
7 a15 1006 wak 1 bal

worksheet 2
a b c d e
1 code item# name SUBC plant
2 a12 1001 s1f 1 bal
3 a12 1007 win k aok
4 a14 1005 plc 1 aok


For cell e6 in worksheet 1, I want to return the value of column e in
worsheet 2 where,

1. b6 in wksht1 = value from b:b in wksht 2 and
2. d6 in wksht1 = value from d:d in wksht2

I hope this makes sense. Any ideas?