Functions

[Guest]

Ok here is a hard one for someone who has really good excel skills. I have to
sheets that i want to combine. These sheets have 2 columns each. They each
share a column that is the same. I want to put whats is the unshared colum of
one into the other so i dont have to use each work sheet to look up
information. Well itll be easier to tell you what it is. Both list my part
numbers. One sheet shows the part number then the price next to it. The other
shows the part number then the vendor for it. I want to combine it so one
sheet will show price and vendor. I cant copy and paste because there are a
few part numbers that arent on each sheet (25% or so) there for i wouldnt be
able to line it up. Is there a if then function that will look for part
numbers that match and then past the cell that i need into the other one. I
can combine into one sheet then do it soe it can just be on same sheet and
side by side so my function would be sorta like If
A1(partnumber)=C1(partnumber) then paste B1 to E1. I just dont know how to
write the function. I would be ever so gratefull for any help. Thanks

 

[JulieD]

Hi

you can use a VLOOKUP for this and then copy & paste special - values the
result

e.g.
sheet 1
Part Number Price

sheet 2
Part Number Vendor

in sheet 1 add in the vendor heading
then in cell C2 of this sheet type
=VLOOKUP(A2,'Sheet 2'!A2:B1000,2,0)
this says lookup the value in A2 (ie the part number)
in sheet 2 column A
and return the associated information from the 2nd column of this sheet
where there is an exact match.

(change the B1000 to the reference of your last line of data in sheet 2)

Hope this helps
cheers
julieD

 

[R.VENKATARAMAN]

sheet 1 is
part no. price
a 1
s 2
d 3
g 5

in sheet 2 the entry
part no. vendor
a q
s w
d e
f r
g t

insheet 2 next to cell <q> type
=VLOOKUP(C5,Sheet1!C59,2,FALSE)

copy this value down

you will get
part no. vendor price
a q 1
s w 2
d e 3
f r #N/A
g t 5

in this sheet the price of part f is not avilable because there is no such
part in sheet1

do ;you get the hang of it. see help <VLOOKUP>

 

[Guest]

I would suggest a VLOOKUP function, it will try to match a value in a cell
(part number) and gives you the choice of selecting the return value from a
column in the same row it "matched".

Assume you have Part # and Price on Sheet1 and Part # and Vendor on Sheet2
I entered the function on Sheet1 next to price to match the part # and pick
up vendor name from sheet2.

C D E
Part # Price Vendor
123 2 =VLOOKUP(C3,Sheet2!C4200,2,FALSE)
456 4
789 6
789 6

C3 is the Part# you want to match e.g. 123.
Sheet2!C4200 is location of the other list (do not include headings)
2 is the column number you want to copy the value from (Vendor is column 2)
FALSE ensures that it will find an exact match, it will return #N/A if no
match found.

I hope this is clear.
cheers,
Nadia