Excel 2007 : Match and insert select data

I have two sheets:

Sales
Item Item Description Qty Amount Brand Cost
000892 Samco Hose Clamp kit, CBR600RR 07-11 1.00 28.00 Samco
003767 GILLES ACM SAFETY LOCK-18MM 1.00 65.21 Gilles
004964 BST Carbon Fiber Wheel Set; CBR1000RR 04-07 1.00 3117.67 BST Wheels
005 Spectro Platinum 4 Full Synthetic, 15W50, 1 Liter 4.00 33.76 Spectro Oil

and Cost
Item Source Name Item Description Qty Cost Price
000892 OPP Racing Samco Hose Clamp kit, CBR600RR 07-11 1.00 28.00
002229 OPP Racing LighTech Frame Sliders with Magnesium Pucks (No Cut) CBR954 1.00 65.77
003767 OPP Racing GILLES ACM SAFETY LOCK-18MM 1.00 65.21
004724 OPP Racing LighTech Adjustable Rearsets with Folding Foot Pegs GSXR600/750 01-05 1.00 408.77
004745 OPP Racing LighTech Toe Peg for Brake and Clutch Levers RFTR52 1.00 16.77


I want to match the items in column A (Item) on the cost sheet to column A on the sales sheet. If there is a match, I want to insert the corresponding value in column E (Cost Price) on the cost sheet, to column F(Cost) on the sales sheet.

I have been playing with this for quite some time now, is there an easy way to do this?


Patrick

You get better help if you post an Excel-example of your workbook, without confidential information.

Fair enough. I have attached the workbook. I am trying to get my head around Excel. I just got a new job and now I'm using Excel everyday, all day.

Any help is very much appreciated!

Originally Posted by Marcol

To do as you ask is not difficult, several lookup functions can be used, I tend to use INDEX() & MATCH().
In F2

=INDEX(Cost!E:E,MATCH(A2,Cost!A:A,0))

Drag/Fill Down

However this reveals some interesting points.

1/. In several cases the figure from Sheet"Cost" doesn't match the value in Sheet"Sales" Amount (Flagged FALSE in Column G:G)

2/, Assuming that "Amount" is a total cost, if you divide by "Qty" in Column I:I you should get a Unit Cost.

3/. Comparing this to Column F we still get several mis-matches (Flagged FALSE in Column J:J)
This indicates that sheet "Sales" has costs based on a different time period i.e. old and new prices.
Perhaps even the same product from different suppliers.
If the sheets are filtered on Item "006", you'll see what I mean.

4/. Several Codes have no match, #N/A.

What should be done to allow for this?

Thanks, that was very helpful! There aren't matches for all items, so I am just going to ignore it. Thanks again!