Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merge data from two separate tables
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merge data from two separate tables
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()]) "brownmre" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge data from two separate tables | Excel Discussion (Misc queries) | |||
How can I refresh data in pivot tables lcoated in separate tabs? | Excel Discussion (Misc queries) | |||
How can I refresh data in pivot tables lcoated in separate tab | Excel Discussion (Misc queries) | |||
How can I refresh data in pivot tables lcoated in separate tab | Excel Discussion (Misc queries) | |||
how do I merge data from separate spreadsheets | Excel Discussion (Misc queries) |