Help with spreadsheet

[gavin]

Hello,

I have 2 spreadsheets and I would like to compare the 2, on one I have a
serial # and a name and on the other I have a serial # and a date. I would
like to combine the 2 spreadsheets into one by matching up the 2 serial
#'s... does anyone know of a way to do this?

Thanks
Gavin...

 

[Don Guillett]

A simple VLOOKUP should do it. Look in the help index

 

[gavin]

trying to use vlookup and not getting any results... this is what I am
doing...

sheet1 - has Serial# and Model #
sheet2 - has Serial# and Computer Name

this is what I have in the formula

=VLOOKUP(E15,Sheet2!A1:B557,2,FALSE)

E15= cell that has the serial # in sheet1
Sheet2!A1:B557 = all values on sheet 2
2 = column # (this is the serial# column)
False = exact match

not sure where I am going wronng...

Thanks
Gavin....

 

[Gord Dibben]

Example only................

sheet1 has serial numbers in column E and model numbers in column F

sheet2 has same serial numbers in column A and Computer Name in column B

In G1 of sheet1 enter =VLOOKUP(E1,sheet2!$A$1:$B$557,2,false)

Copy that down column G

BTW...........what happened to the "date" on sheet2?


Gord Dibben MS Excel MVP

 

[gavin]

here's the catch, the serial numbers are not the same, the sheet2 may or may
not have the serial# in sheet1, what I want to do is match up the ones that
are in sheet2 with sheet1 and if there is no match just put a "no match" in
the cell... is this possible... for example

Column A Column B

Computer Name Serial #

computer1 1234 (sheet2)
computer2 1235
computer4 1237


Model # Serial #

Dell 620 1234 (Sheet1)
Dell 620 1235
Dell 620 1236
Dell 620 1237



Model # Serial # Computer Name

Dell 620 1234 computer1 (sheet1 Column C) this
us what I am trying to achive...
Dell 620 1235 computer2
Dell 620 1236 No Match
Dell 620 1237 computer 4

Thanks
Gavin...

 

[Don Guillett]

vlookup has to lookup columns to the right. So, use MATCH to find the serial
number in col B and then use that within an INDEX formula on col A to get
the computer name. Look in the help index for both.

 

[Avi]

Use the match index as suggested by Don. Here is the sample formula
for cell C2-

=index(Sheet1!$A$1:$A$10,match(b2,Sheet1!$B$1:$b$10,1),1)

 

[gavin]

I did what you said and my colomn returns nothing...I have attached the
spreadsheet, maybe you can see where I am going wrong.

Thanks
Gavin...


Use the match index as suggested by Don. Here is the sample formula
for cell C2-

=index(Sheet1!$A$1:$A$10,match(b2,Sheet1!$B$1:$b$10,1),1)

 

[Don Guillett]

=IF(ISNA(MATCH($B2,Sheet2!B:B,0)),"",INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0)))