Is this possible -

[RTowles]

I have an excel report with over 700 lines of data. The report has everything but the pricing. There are duplicate values in this list. So, A3 may have the same information as A5. To save time I would like a formula that would find the Items in one cell, compare it to the items in another cell, and copy the pricing for that cell.

ex
i would create 2 more columns next to it with the comparing data.
Cell A has the data from the report.
Cell B has a price field that's empty
Cell C is me inserting a column and putting a standard list (500+ items)
Cell D is me inserting a column and putting the price of the items from Cell C

Item Names/ Price / Cell C / Cell D
A1. item 2 / ------ / item 0 / $5
A2. item 3 / ------ / item 1 / $10
A3. item 2 / ------ / item 2 / $3
A4. item 0 / ------ / item 3 / $1

I would need the formula to recognize that cell A1 and A3 are the same items and would auto populate B Cell (Price) B1 and B3 with $3; cell B2 would populate with $1, and cell B4 would populate $5.

In my head I was trying the If Formula: If A:A is the same as C:C put number from D:D.

The problem I'm getting is how does Excel know which number in DD to put. I don't want to create 700 formulas.

Any ideas

 

[Chihiro]

You'd create 2 separate table.

1 as you currently have, but without Column D.

Then 2nd with Item names (unique only) and prices for each.

Then you'd perform lookup using either Index(Prices,Match(Item, Item names, 0)) or some other construct.

 

[RTowles]

I apologize for my ignorance.

So keep the 1st table (without D or without C and D?).
Create the 2nd table (on another sheet)
Then use ... vlookup using the index to match each item?

did i understand this correctly?

 

[RTowles]

Nevermind.

I had some coffee and i understand now. It worked awesomely and I appreciate your help. I guess I was overthinking it!