how 2 merge 2 tables (worksheets)

[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 [email protected] 212
3 [email protected] 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 [email protected] 212
2 ron s la
3 dan r ca [email protected] 718

 

[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.

 

[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)

 

[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.

 

[Herbert Seidenberg]

Used Pivot Table, VBA and
Excel 2003 Lists. No formulas used.
Data can be located anywhere,
can have odd rows and columns,
be in random order and incomplete.
Dynamic data ranges provided for easy editing.
http://www.freefilehosting.net/download/3d94b

 

[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

 

[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

 

[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