Merge data from two separate tables

[brownmre]

I want values from table two to be copied into table one where 2 cells from a
row in table 2 matches 2 cells in a row from table one. Examples: if b6
(table 1) equals a value in column b (table 2) and d6 (table 1) equals a
value in column d (table 2), then e6 (table 1) will equal value “aok†from
column d (table 2). Any ideas?


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

 

[Duke Carey]

If you are guaranteed to have only one matching row then you can use a
combination of MATCH() and INDEX(). You'd use the MATCH() function to
compare the combination of values in Table 1 columns B & D to the whole
columns B & D in table 2, like so (entered as an array formula by pressing
Ctrl-Shift-Enter)

=match(b6&d6,'other sheet'!$b$2:$b$2000&'other sheet'!$d$2:$d$2000,0)

That will tell you the row in the table that matches, or give you an error
if there is no match. To get the value from column E, use INDEX()

=INDEX('other sheet'!$e$2:$e$2000, match(b6&d6,'other
sheet'!$b$2:$b$2000&'other sheet'!$d$2:$d$2000,0))

If there is no match, you'll get an error, so you may want to wrap the whole
thing in an IFERROR() [if you have Excel 2007], or an If(iserror()) if you
have an earlier version

The combined formula must be entered with Ctrl-Shift-Enter

=INDEX('other sheet'!$e$2:$e$2000, [number from MATCH()])