Unique values from dynamic list

Hi all,

I am having a hard time finding a way to pull the unique results of an array based on certain search criteria. That's confusing so let me associate it with a picture (see attachment)

I want to only find the unique lots associated with the product ID. Below is where I am at with my formula. It is pulling all of the lots associated with that product ID, not just the unique values.

Code:

---

=IFERROR(INDEX(Table1[[Lot_Code]:[Lot_Code]],AGGREGATE(15,3,(Table1[[Product_ID]:[Product_ID]]=$F9)/(Table1[[Product_ID]:[Product_ID]]=$F9) _
*ROW(Table1[[Product_ID]:[Product_ID]])-ROW(Table1[[#Headers],[Product_ID]]),COLUMNS($G$7:G8))),"")

---

I can't think of a one-formula approach that wouldn't be a difficult-to-maintain mega-formula!
So in the attached there are 3 offerings:
1. By the far the easiest way to get the data you want (though not in the same arrangement) is a very simple pivot table. That's on Sheet1 at cell A14. It'll get you the data in seconds, robustly.
2. Using a helper column (Column E on Sheet1) and an array-entered formula in cell G9, copied across and down.
3. Using your formula, but acting on a new table (on Sheet1 (2) at cell D14) created by Advanced Filter for Unique rows. Your formula is normally entered in G9 and copied across and down.

Hi,

If Product ID is in F7 and if data are in F8, F9, F10... in G8 put this array formula (Ctrl + Shift + Enter)

Code:

---

=IFERROR(INDEX($C$2:$C$30,SMALL(IF(FREQUENCY(IF($B$2:$B$30=$F8,MATCH($C$2:$C$30,$C$2:$C$30,0)), _
ROW($C$2:$CB$30)-ROW($C$2)+1),ROW($C$2:$C$30)-ROW($C$2)+1),COLUMNS($G$8:G8))),"")

---

Then "drag" formula down and right or vice versa.

PS Obviously changes the data range according to the data in the file.

I believe using a pivot table will be the easiest way to do this. Thanks for the help