Return Multiple UNIQUE Match Values in Excel

Highlighted
Occasional Visitor

Return Multiple UNIQUE Match Values in Excel

Hi all,

I can only imagine that this topic has been covered before. It is not the easiest to search down exactly what I need, so please forgive me.

I have followed @Leila Gharani's tutorial "Return Multiple Match Values in Excel" [ LINK ], and succeded in transforming it to fit into my scope. Thank you Leila, for a great video!

Now, I need to do a small adjustment to make sure the same value is not returned multiple times. Here is my scope:
I have a Bill of Materials, where all material number has a reference designation. This is a sample of the list:

 Reference designator Material No. -200-08-01-U1 193819 -200-08-01-U1 193666 -200-08-01-V1 092008 -200-08-01-V1 29082290 -200-08-01-V1 29082290 -200-08-01-V1 29082290 -200-08-01-V1 1343567674 -200-08-01-V1 092008 -200-08-01-W1 29064039 -200-08-01-W1 29064039 -200-08-01-W2 29078487

As you can see, some of the reference designations has multiple material numbers.

Now, in another sheet, I need to do a list, following this principle:

 (A) Reference designator (B) Mat. No. #1 (C) Mat. No. #2 (D) Mat. No. #3 -200-08-01-V1 092008 29082290 1343567674

I have succeeded in getting the first match from the top in column B, the second in column C and third in column D. But in this particular case, match number 2 and 3 from the top is 29082290. So I need the formula to include a check if the value in (C) is the same as (B) and then skip it and so on.

I hope this describes well enough, what I'm trying to do.

In the attached, I've included some sample data. In sheet "Mapping", the relevant coloumns is marked with green, and the lookup is don in "PDMLink BOM", coloum D & E.

I'm looking very much forward to hear if any of you have any inputs!

Best regards,

Mikael

0 Replies