VLOOK UP duplicate data?

[Couchy]

I have a worksheet that lists parts, with all there required date in one sheet, in the next sheet i have a material grade and a price that is pulled through from a different work book.

Within the first sheet i have a drop down box that when the grade selected the price populates the next cell, simple V-Look up apparently.

The problem with this is there are hundreds of parts that use the same material grades, but it wont allow duplicate prices from the VLook up, how can i get around this?

Thanks

[JamesT1]

Hi Couchy

The VLOOKUP will only give you the first match it comes to on your list, if each part uses an individual part number you could use this for the equation rather than the grade

[Couchy]

The grades change to much so the way it's set up is the more efficient way. Are there no functions or formulas to get around this to allow duplicates?

[JamesT1]

Hi Couchy

Can you post a shortened version of your sheet to give better idea of what you require
Jim

[Pete_UK]

Yes, there are. What you need to do is to introduce some other formula (often using COUNTIF) which will give you a unique reference for each of your duplicate entries, and then your VLOOKUP formula can be amended to look for the unique reference (you may need to change it to INDEX/MATCH). Please post an example of your data, so we can see how to set it up for you.

Hope this helps.

Pete

[Couchy]

Here is the example, the vlook up and drop are on material and material cost.

[Pete_UK]

I just changed the formula in F2 of the Parts sheet to this:

=VLOOKUP(E2,'MATERIAL PRICE'!$A$2:$B$25,2,FALSE)

(i.e. made the ranges of the lookup table absolute), and then copied down - this removed the error messages you were getting.

However, I can't see any duplicates in the other sheet, so I'm not sure how this sample relates to the problem you described earlier.

Hope this helps.

Pete

[Couchy]

Thanks, I've just been clumsy and removed the $ signs out so i wasnt getting the same value. Confusing myself, years since i've used excel

[Pete_UK]

So, is the problem solved (if so please mark it as such), or do you still have an issue with duplicates in the second sheet?

Pete

[Couchy]

No no problems at all. how do i mark as solved?