Duplicated lookup value needing unique return

[russborup]

I have data that can be duplicated but will need to return specific serial numbers.
In one tab i will have the following:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Name[/TD]
[TD="class: xl66, width: 64"]Model[/TD]
[TD="class: xl66, width: 64"]Color[/TD]
[TD="class: xl66, width: 64"]PO[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789
[/TD]
[/TR]
</tbody>[/TABLE]

I will receive serial numbers back referencing duplicated criteria that need to be populated in the existing tab.

Example:

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Name[/TD]
[TD="class: xl66, width: 64"]Model[/TD]
[TD="class: xl66, width: 64"]Color[/TD]
[TD="class: xl66, width: 64"]PO[/TD]
[TD="class: xl67, width: 64"]Serial#[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]123[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]234[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]345[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]456[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]567[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]123[/TD]
[TD="class: xl66"]White[/TD]
[TD="class: xl66"]PO123[/TD]
[TD="class: xl67"]678[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]654[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]543[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]432[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]321[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]210[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]233[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66"]789[/TD]
[TD="class: xl66"]Black[/TD]
[TD="class: xl66"]PO789[/TD]
[TD="class: xl67"]322[/TD]
[/TR]
</tbody>[/TABLE]

What can I do to have the serial number from the second example populate to the first example?
**Note that the serial numbers will come in at different times for different PO's.

 

[JonXL]

Are they always going to be identical like this in terms of their layout?

Right now it looks like you have two sheets that are exactly the same but for the presence of an additional column in the second.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr][tr][td]

1​

[/td][td]Name[/td][td]Model[/td][td]Color[/td][td]PO[/td][td]#[/td][td][/td][td][/td][td]Name[/td][td]Model[/td][td]Color[/td][td]PO[/td][td]Serial#[/td][/tr]
[tr][td]

2​

[/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

123​

[/td][td][/td][td][/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

123​

[/td][/tr]
[tr][td]

3​

[/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

234​

[/td][td][/td][td][/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

234​

[/td][/tr]
[tr][td]

4​

[/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

345​

[/td][td][/td][td][/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

345​

[/td][/tr]
[tr][td]

5​

[/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

456​

[/td][td][/td][td][/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

456​

[/td][/tr]
[tr][td]

6​

[/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

567​

[/td][td][/td][td][/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

567​

[/td][/tr]
[tr][td]

7​

[/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

678​

[/td][td][/td][td][/td][td]ABC[/td][td]

123​

[/td][td]White[/td][td]PO123[/td][td]

678​

[/td][/tr]
[tr][td]

8​

[/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

210​

[/td][td][/td][td][/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

654​

[/td][/tr]
[tr][td]

9​

[/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

233​

[/td][td][/td][td][/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

543​

[/td][/tr]
[tr][td]

10​

[/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

321​

[/td][td][/td][td][/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

432​

[/td][/tr]
[tr][td]

11​

[/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

322​

[/td][td][/td][td][/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

321​

[/td][/tr]
[tr][td]

12​

[/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

432​

[/td][td][/td][td][/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

210​

[/td][/tr]
[tr][td]

13​

[/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

543​

[/td][td][/td][td][/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

233​

[/td][/tr]
[tr][td]

14​

[/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

654​

[/td][td][/td][td][/td][td]XYZ[/td][td]

789​

[/td][td]Black[/td][td]PO789[/td][td]

322​

[/td][/tr]
[/table]

In E2 control+shift+enter, not just enter, and copy down:

=SMALL(IF($H$2:$H$14=A2,IF($I$2:$I$14=B2,IF($J$2:$J$14=C2,IF($K$2:$K$14=D2,$L$2:$L$14)))),
    COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:C2,$C2,$D$2:D2,$D2))

 

[russborup]

Are they always going to be identical like this in terms of their layout?

Right now it looks like you have two sheets that are exactly the same but for the presence of an additional column in the second.

The Data will be not be in the same format, nor will it be
One sheet that will need to be populated will have much more data.
I will be receiving the other sheet which will only have the name, model, color, po and serial number.

 

[russborup]

I tried this and it worked for this particular set.
The issue is that the data will not be as organized as I have laid out.
On the sheet that will need to be filled, it will have much more columns added.
The sheet that will have the serial numbers will not all come in together, nor will they be lined up as clean.
I have tried V lookup with a concatenate, but that will only return the first result.
I could have up to 40 identical records that will need to be populated with unique serial numbers.

 

[Aladin Akyurek]

@ russborup

1. When you seek for help, you should samples that re representative of your data.

2. Did you test the suggestion with a representative sample at all?

 

[russborup]

Thank you Mr. Akyurek
I did try your example, and it did not work with my real data.
Taking your advice, here is live data

One the first sheet I have product that I am waiting for serial numbers to be provided.

[TABLE="width: 997"]
<colgroup><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 997"]
<colgroup><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Entry Date[/TD]
[TD]Ship Date[/TD]
[TD]Status[/TD]
[TD]Ord ID[/TD]
[TD]PO[/TD]
[TD]SERIAL#[/TD]
[TD]Description[/TD]
[TD]Color[/TD]
[TD]City[/TD]
[TD]ST[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]2/21/2018[/TD]
[TD]CC[/TD]
[TD]30022[/TD]
[TD]AZ337[/TD]
[TD][/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]PHOENIX[/TD]
[TD]AZ[/TD]
[TD]98145[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]2/21/2018[/TD]
[TD]CC[/TD]
[TD]30023[/TD]
[TD]AZ337[/TD]
[TD][/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]PHOENIX[/TD]
[TD]AZ[/TD]
[TD]98145[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]2/21/2018[/TD]
[TD]CC[/TD]
[TD]30024[/TD]
[TD]AZ337[/TD]
[TD][/TD]
[TD]BEARING HOUSE[/TD]
[TD]SILVER[/TD]
[TD]PHOENIX[/TD]
[TD]AZ[/TD]
[TD]98145[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]2/21/2018[/TD]
[TD]CC[/TD]
[TD]30025[/TD]
[TD]AZ337[/TD]
[TD][/TD]
[TD]BEARING HOUSE[/TD]
[TD]SILVER[/TD]
[TD]PHOENIX[/TD]
[TD]AZ[/TD]
[TD]98145[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30026[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30027[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30028[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30029[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30030[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30031[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30032[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30033[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30034[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD]3/6/2018[/TD]
[TD]CC[/TD]
[TD]30035[/TD]
[TD]AZ336[/TD]
[TD] [/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TUCSON[/TD]
[TD]AZ[/TD]
[TD]98451[/TD]
[/TR]
</tbody>[/TABLE]

I receive the following providing the Serial numbers that I need to populate in the above sheet:

[TABLE="width: 662"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Order Qty.[/TD]
[TD]PO Number[/TD]
[TD]PO date[/TD]
[TD]Description[/TD]
[TD]Color[/TD]
[TD]Serial#[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ337[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5B10J6408786[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C11J6400311[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C13J6400312[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C14J6386369[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C14J6386372[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C16J6386373[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C16J6387023[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C17J6386365[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C17J6388262[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C17J6400961[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ336[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5C18J6386374[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ337[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]SILVER[/TD]
[TD]QAMDN5L10J6404897[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ337[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]WHITE[/TD]
[TD]QAMDN5R11J6388810[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AZ337[/TD]
[TD]2/16/2018[/TD]
[TD]BEARING HOUSE[/TD]
[TD]SILVER[/TD]
[TD]QAMDN5X10J6399331[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Which fields must match up?

 

[russborup]

The PO Description and Color must match to return the serial number

 

[Aladin Akyurek]

The PO Description and Color must match to return the serial number

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

E​

[/td][td]

F​

[/td][td]

H​

[/td][td]

L​

[/td][td]

N​

[/td][td]

Q​

[/td][td]

R​

[/td][/tr][tr][td]

1​

[/td][td]PO[/td][td]SERIAL#[/td][td]Color[/td][td][/td][td]PO Number[/td][td]Color[/td][td]Serial#[/td][/tr]
[tr][td]

2​

[/td][td]AZ337[/td][td]QAMDN5B10J6408786[/td][td]WHITE[/td][td][/td][td]AZ337[/td][td]WHITE[/td][td]QAMDN5B10J6408786[/td][/tr]
[tr][td]

3​

[/td][td]AZ337[/td][td]QAMDN5R11J6388810[/td][td]WHITE[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C11J6400311[/td][/tr]
[tr][td]

4​

[/td][td]AZ337[/td][td]QAMDN5L10J6404897[/td][td]SILVER[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C13J6400312[/td][/tr]
[tr][td]

5​

[/td][td]AZ337[/td][td]QAMDN5X10J6399331[/td][td]SILVER[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C14J6386369[/td][/tr]
[tr][td]

6​

[/td][td]AZ336[/td][td]QAMDN5C11J6400311[/td][td]WHITE[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C14J6386372[/td][/tr]
[tr][td]

7​

[/td][td]AZ336[/td][td]QAMDN5C13J6400312[/td][td]WHITE[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C16J6386373[/td][/tr]
[tr][td]

8​

[/td][td]AZ336[/td][td]QAMDN5C14J6386369[/td][td]WHITE[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C16J6387023[/td][/tr]
[tr][td]

9​

[/td][td]AZ336[/td][td]QAMDN5C14J6386372[/td][td]WHITE[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C17J6386365[/td][/tr]
[tr][td]

10​

[/td][td]AZ336[/td][td]QAMDN5C16J6386373[/td][td]WHITE[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C17J6388262[/td][/tr]
[tr][td]

11​

[/td][td]AZ336[/td][td]QAMDN5C16J6387023[/td][td]WHITE[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C17J6400961[/td][/tr]
[tr][td]

12​

[/td][td]AZ336[/td][td]QAMDN5C17J6386365[/td][td]WHITE[/td][td][/td][td]AZ336[/td][td]WHITE[/td][td]QAMDN5C18J6386374[/td][/tr]
[tr][td]

13​

[/td][td]AZ336[/td][td]QAMDN5C17J6388262[/td][td]WHITE[/td][td][/td][td]AZ337[/td][td]SILVER[/td][td]QAMDN5L10J6404897[/td][/tr]
[tr][td]

14​

[/td][td]AZ336[/td][td]QAMDN5C17J6400961[/td][td]WHITE[/td][td][/td][td]AZ337[/td][td]WHITE[/td][td]QAMDN5R11J6388810[/td][/tr]
[tr][td]

15​

[/td][td]AZ336[/td][td]QAMDN5C18J6386374[/td][td]WHITE[/td][td][/td][td]AZ337[/td][td]SILVER[/td][td]QAMDN5X10J6399331[/td][/tr]
[/table]

In F2 control+shift+enter, not just enter, and copy down:

=INDEX($R$2:$R$15,SMALL(IF($N$2:$N$15=E2,IF($Q$2:$Q$15=H2,ROW($R$2:$R$15)-ROW($R$2)+1)),
    COUNTIFS($E$2:$E2,$E2,$H$2:$H2,$H2)))