How can I match up values from 2 sheets and info in adjoining cell

[Guest]

I've got two spreadsheets: our current pricing on products that we get from a
particular vendor and the new pricing for 2006 that they've just sent us.

I think that I may have to put both of these spreadsheets in the same
workbook, as multiple sheets or something.

What I'm trying to do is compare the current pricing and the new pricing to
see where we need to make adjustments. I was hoping to find a way to look at
a particular part number in the first sheet and find that number in the
second sheet, then take the price for that item in the second sheet and enter
it into a new column by that item in the first sheet.

So... the first sheet would have have two columns: part number and current
pricing. The second sheet would have two columns: part number and new
pricing. On the first sheet, I need a third column to bring in the new
pricing from the second sheet.

The final version of the first sheet would show part number, current price
and new price.

Is there a way to do this?

Thank you in advance for any assistance you may be able to provide.

 

[Guest]

One way is to use VLOOKUP ..
Assume we have
In sheet: Current
Part# Price NewPrice
1111 10 ?
1112 20 ?
etc

and, in sheet: New
Part# Price
1111 15
1112 25
etc

("New" will house the new prices)

Then in sheet: Current
Put in C2:
=IF(A2="","",VLOOKUP(A2,New!A:B,2,0))
Copy C2 down as far as required

Col C will return the prices from "New" for the part# in col A

Perhaps better (but longer) with a dash more error trapping,
we could also put in C2, and copy down:
=IF(A2="","",IF(ISNA(MATCH(A2,New!A:A,0)),
"Part# not found in New",VLOOKUP(A2,New!A:B,2,0)))

The above will return the phrase: "Part# not found in New"
for unmatched part#s, if any, instead of "ugly" #N/As

Adapt to suit ..

 

[Guest]

Would this formula still work if the part numbers on each sheet are not in
exactly the same row? They've added some new items to their listing, so they
are not in the same order, at all.

Thanks for your reply, Max!

 

[Guest]

I figured it out, Max.... THANK YOU VERY MUCH!

Would this formula still work if the part numbers on each sheet are not in
exactly the same row? They've added some new items to their listing, so they
are not in the same order, at all.

Thanks for your reply, Max!

 

[Max]

=IF(A2="","",VLOOKUP(A2,New!A:B,2,0))

on each sheet are not in exactly the same row?

Yes, of course <g>

That's the main benefit of using formulas such as VLOOKUP (with 4th param
set to zero for exact matches)

But admit it could have been better illustrated in the example descript
given in the response.

---