how 2 merge 2 tables (worksheets)


A

adam

if i have a table/sheet like this:

id# first last address
1 bob a ny
2 ron s la
3 dan r ca

and a different one:
id# email phone
1 (e-mail address removed) 212
3 (e-mail address removed) 718

is it posible to merege so where ever Id# sheet1 = Id# sheet 2 it will auto
add in that row the cells with the data?

example of final result i need:
id# first last address email phone
1 bob a ny (e-mail address removed) 212
2 ron s la
3 dan r ca (e-mail address removed) 718
 
Ad

Advertisements

S

Sean Timmons

=INDEX(TABLE YOU ARE REFERENCING,MATCH(THE ID # FROM CURRENT TABLE,THE COLUMN
IN REFERENCED TABLE THAT HOLDS THE ID #),MATCH(THE COLUMN HEADER YOU WANT,THE
ROW IN REFERENCED TABLE THAT HAS YOUR COLUMN HEADERS))

So, if the first table you listed is on Sheet 1, in A1:C3 and your result
table is on Sheet2, say, A1:E4, where e-mail address is column E:

=INDEX(Sheet1!$A$1:$C$3,MATCH($A2,Sheet1!$A$1:$A$3),MATCH(E$1,Sheet1!$A$1:$C$1))

returns the e-mail address. Absolute values have been set to allow you to
copy this formula all the way down and to the right.
 
A

adam

i didnt get it
if my first table is a1:d4 with tha same example i put before and my second
table on sheet 2 is a1:c3 what do i do? (take notice that in sheet 2 one of
the id# is missing. (the number of rows on the sheets are diff)
 
S

Sean Timmons

Since you want your resulting table to have the additional rows that are in
table 1, you would want to put the formula into the first table, not the
second. You would add the e-mail and phone column headers to the first table
and put the formula there. Change the SHEET1 sheet name reference to whatever
the name of your second tab is and ensure you are capturing your entire table
with the cell references.
 
A

adam

sean can u give me the exact formula to put in sheet1 cell E2
acording the example i gave?(so it will brind the data on sheet 2 to sheet1)
i added the 2 headers in sheet 1
 
Ad

Advertisements

A

adam

Sean,
When i put the formula =INDEX(Sheet2!B:B,MATCH(A3,Sheet2!A:A)) in sheet 1
cell E2 it worked good. The only problem is that sheet 2 wont/dosent have all
id# like sheet 1 has and when i put the formula, even though there there was
no id#2 on sheet 2 it copied the value from the above cell instead of leaving
cell blank
 
Ad

Advertisements

A

adam

Sean,
When i put the formula =INDEX(Sheet2!B:B,MATCH(A3,Sheet2!A:A)) in sheet 1
cell E2 it worked good. The only problem is that sheet 2 wont/dosent have all
id# like sheet 1 has and when i put the formula, even though there there was
no id#2 on sheet 2 it copied the value from the above cell instead of leaving
cell blank
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top