Lookup unique value in column of duplicates; return result based on value adjacent column.

[EdBexson]

Hi all,

New to the forum, so please forgive any newbie mistakes.

I have a list of 40,000 unique part numbers, and a seperate database of 500,000 part numbers.

The database has many duplicate part numbers, split by Plant (Ownership). The Part/Plant combination on each row is unique, and other columns in that row contain unique information, i.e. Demand and Sales.


To illustrate the database:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Part No
[/TD]
[TD]Plant (Owner)
[/TD]
[TD]Demand Value
[/TD]
[TD]Sales
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]£10,986
[/TD]
[TD]£9,401
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[TD]£8,098
[/TD]
[TD]£4,871
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A
[/TD]
[TD]£27,856
[/TD]
[TD]£33,401
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]C
[/TD]
[TD]£50,605
[/TD]
[TD]£11,398
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]£7,096
[/TD]
[TD]£7,984
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]B
[/TD]
[TD]£46,873
[/TD]
[TD]£56,012
[/TD]
[/TR]
</tbody>[/TABLE]












What I would like to do is use my list of parts, and look them up against the database. However - as their are duplicate part numbers (see above), I would like to return only rows beloning to Plant B; BUT if Plant B does not exist for that part number, I would like it to return Plant A, and so on. I'd like this like a normal VLOOKUP, so I can return values further along the row, such as Demand Value and Sales.

Basically, I want a priority lookup, returning values based on a hierarchy of plants.

I would also like these to be interchangable, i.e.

[TABLE="width: 500"]
<tbody>[TR]
[TD]IMPORTANCE
[/TD]
[TD]PLANT
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]C
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
Ed

 

[Mackers]

You can do this in a single cell with a nested IF using INDEX and MATCH based on the cells in your Importance/Plant table. If there are lots of plant types (tens or hundreds) this will be a nightmare so this might not be a reasonable solution, but if there are 3 as in your example it is workable. Alternatively you can do this in one cell per plant type, i.e. your second table of Importance/Plant would have a third column that would return the value for the combination of Plant and Part - this would be pretty trivial using SUMIFS() on the Plant and Part.

Do either of these sound suitable? As an example of the SUMIFS method, for your first table being on the 'Data' sheet and your second table being on the 'Importance' sheet, with the part number you want to check being in a field called 'PartNo':

Demand value =sumifs(Data!C:C, Data!A:A, PartNo, Data!B:B, Importance!B2)
Sales =sumifs(Data!D:D, Data!A:A, PartNo, Data!B:B, Importance!B2)