How do I match 2 cols on two books and get price info for all matc

[Guest]

Hi folks I have a major problem. I have done this manually in the past but it
has just taken so long and is so tedious that I am sure there is a quicker
way using VLOOKUP or some other function however I have been having no succes
so was hoping some kind hearted individual out there could help me out....
anyhows here'e my problem.

I have two worksheets one is a Reference Look Up table , we shall call this
workbook A. Its columns are as follows :
WORKBOOK A

ColA Col B Part No Desc ColE ColF ColG
===== ====

I also have another Worksheet which is a pricelist we shall call this
Workbook B. Its columns areas follows :
WORKBOOK B

PART NO DESC PRICE
====== ==== =====

What I require is to add a Price Column to Workbook A in column H. Any Part
Number in Workbook A that matches Part No in Workbook B should be copied and
inserted in Workbook A in the new price column for that part.

- This is made more complicated as the Part No's in Workbook A may
appear many times (i.e. a certain part is shared by many different products),
however it will only appear once on the pricelist - Workbook B.

- Also some part no's may exist on Workbook A and not on Workbook B,
similarly some part no's may exist on Workbook B but not on Workbook A. In
any of these cases this information needs to be written either to a new
worksheet or added to the bottom of the data in Workbook A.


Hope somewhere out there can provide me some help as my head hurts from
banging it against this monitor !! lol

Thanks folks

 

[Guest]

To obtain price:

=if(ISNA(VLOOKUP(C2,Workbookb!A1:C100,3,0)),"Part not
found",VLOOKUP(C2,Workbookb!A1:C100,3,0))

To obtain description:

=if(ISNA(VLOOKUP(C2,Workbookb!A1:C100,2,0)),"Part not
found",VLOOKUP(Cc2,Workbookb!A1:C100,2,0))

Copy down as required and change range of w/book B as needed.

For mismatches between workbooks A & B you probably need VBA code if want to
write them out to a separate sheet or add to the end of exising

HTH