Search code examples
excel-formulaexcel-2007

Excel 2007 - adding prices to a list of parts using a stock list and a price list

I am certain that there is an easy way to do this, but for some reason this one is sitting in my blind spot...

I have a price catalogue that lists all possible part numbers, and their prices. I have a separate stock list that has been produced by a third party which lists all of the parts that they hold in stock. I need to write a formula that checks if the parts on the stock list are in the price catalogue, and if they are, add the price to the stock list.

For example, if the price catalogue has part number in column A and price in Column B, and I add the part numbers from the stock check to column C, I need to check if they are in A, and if they are, add the price into column D.

Does that make sense?

Thanks!

Solution

  • VLOOKUP is one formula which can help you resolve your problem.

    A combination of INDEX-MATCH-MATCH is another formula which can get you the same result.

    VLOOKUP is a VOLATILE function and takes longer time to work. However, this can be experienced only in large files.

    Some experts have compared the working results of both these and have concluded that VLOOKUP is about 13 times slower than the INDEX-MATCH-MATCH formula combination.