VLOOKUP/TEXTJOIN (or similar) to return multiple unique results in a single cell

[DessertDiva]

I've got Table 1 with tracking numbers for November. I've got Table 2 with tracking numbers and invoice numbers November. I want to have Table 1 look for its tracking # in Table 2 and return to me all invoice numbers where that tracking # appears and return those results in a single cell. I did find a TEXTJOIN formula that will pull all results in a single cell. YAY! That made me so happy until . . .

A tracking number is listed for every single fee associated with it on any given invoice so this TEXTJOIN formula is returning many invoices numbers in that one cell but only 2 of them are unique. Sometimes a tracking # can appear on 3 or more invoices depending on all the extra charges that come in for it over time. How do I get rid of those duplicates. I would like a formula that will return multiple UNIQUE results into one cell.

Also, do you know how I could do that same formula but have Table 1 find its tracking # and return to me invoice #s from two different tables? Sometimes we'll ship something in November but charges for it will come in across both November and December invoices, sometimes they'll even trickle into January/February invoices, too. See Order #8476 in Table 1 below. That tracking # is appearing on both Nov and Dec UPS invoices.

TIA for your help!!!

Here's Table 1 (Summary of all Order/Shipping Info):

Order #	Order Date	Warehouse	Tracking #	Carrier Fee	Is tracking # on Nov UPS?	Is tracking # on Dec UPS?	Is tracking # accounted for?	UPS Invoice #
8263	10/31/2020	Pink	1Z171WF06824495	27.74	TRUE​	FALSE​	TRUE​
8277	11/2/2020	Pink	1Z171WF06827164	22.31	TRUE​	FALSE​	TRUE​
8476	11/19/2020	Pink	1Z171WF06737259	36.64	TRUE​	TRUE​	TRUE​

Here's Table 2 (November UPS):

Order Date	Warehouse	Ship Service Charge Description	Tracking #	UPS Invoice #
11/4/2020	PINK	Entry Prep Fee	1Z171WF06824495	1431875072
11/4/2020	PINK	Brokerage GST	1Z171WF06824495	1431875072
11/4/2020	PINK	Disbursement Fee	1Z171WF06824495	1431875072
11/4/2020	PINK	Ca British Columbia Pst	1Z171WF06824495	1431875072
11/4/2020	PINK	Customs Gst	1Z171WF06824495	1431875072
11/4/2020	PINK		1Z171WF06824495	1431875072
11/4/2020	PINK		1Z171WF06824495	1431875072
11/2/2020	PINK	Standard to Canada	1Z171WF06824495	000000171WF0450
11/2/2020	PINK	UPS carbon neutral	1Z171WF06824495	000000171WF0450
11/2/2020	PINK	Fuel Surcharge	1Z171WF06824495	000000171WF0450
11/3/2020	PINK	Standard to Canada	1Z171WF06827164	000000171WF0450
11/3/2020	PINK	Residential Surcharge	1Z171WF06827164	000000171WF0450
11/3/2020	PINK	UPS carbon neutral	1Z171WF06827164	000000171WF0450
11/3/2020	PINK	Fuel Surcharge	1Z171WF06827164	000000171WF0450
11/9/2020	PINK	Brokerage GST	1Z171WF06827164	1432650786
11/9/2020	PINK	Disbursement Fee	1Z171WF06827164	1432650786
11/9/2020	PINK	Entry Prep Fee	1Z171WF06827164	1432650786
11/9/2020	PINK	QST	1Z171WF06827164	1432650786
11/9/2020	PINK	Pst Quebec	1Z171WF06827164	1432650786
11/9/2020	PINK	Customs Gst	1Z171WF06827164	1432650786
11/9/2020	PINK		1Z171WF06827164	1432650786
11/9/2020	PINK		1Z171WF06827164	1432650786
11/23/2020	PINK	Worldwide Expedited	1Z171WF06737259	000000171WF0480
11/23/2020	PINK	Peak Surcharge - Commercial	1Z171WF06737259	000000171WF0480
11/23/2020	PINK	UPS carbon neutral	1Z171WF06737259	000000171WF0480
11/23/2020	PINK	Duty and Tax Forwarding Surcharge	1Z171WF06737259	000000171WF0480
11/23/2020	PINK	Fuel Surcharge	1Z171WF06737259	000000171WF0480

Here's Table 3 (December UPS):

Order Date	Warehouse	Ship Service Charge Description	Tracking #	UPS Invoice #
11/26/2020	PINK	WW Expedited	1Z171WF06737259	1437220044
11/26/2020	PINK	Disbursement Fee	1Z171WF06737259	1437220044
11/26/2020	PINK	Skr	1Z171WF06737259	1437220044
11/26/2020	PINK	Value Added Tax	1Z171WF06737259	1437220044

 

[AhoyNC]

If your version of 365 has the FILTER function you could use something like below to return data from 1 table. My suggestion would be to put all the data into 1 table adding a date column instead of having different tables for each month.

Book2
	A	B	C	D	E	F	G	H	I
1	Order #	Order Date	Warehouse	Tracking #	Carrier Fee	Is tracking # on Nov UPS?	Is tracking # on Dec UPS?	Is tracking # accounted for?	UPS Invoice #
2	8263	10/31/2020	Pink	1Z171WF06824495	27.74	TRUE	FALSE	TRUE	1431875072, 000000171WF0450
3	8277	11/2/2020	Pink	1Z171WF06827164	22.31	TRUE	FALSE	TRUE	000000171WF0450, 1432650786
4	8476	11/19/2020	Pink	1Z171WF06737259	36.64	TRUE	TRUE	TRUE	000000171WF0480
5
6	Here's Table 2 (November UPS):
7	Order Date	Warehouse	Ship Service Charge Description	Tracking #	UPS Invoice #
8	11/4/2020	PINK	Entry Prep Fee	1Z171WF06824495	1431875072
9	11/4/2020	PINK	Brokerage GST	1Z171WF06824495	1431875072
10	11/4/2020	PINK	Disbursement Fee	1Z171WF06824495	1431875072
11	11/4/2020	PINK	Ca British Columbia Pst	1Z171WF06824495	1431875072
12	11/4/2020	PINK	Customs Gst	1Z171WF06824495	1431875072
13	11/4/2020	PINK		1Z171WF06824495	1431875072
14	11/4/2020	PINK		1Z171WF06824495	1431875072
15	11/2/2020	PINK	Standard to Canada	1Z171WF06824495	000000171WF0450
16	11/2/2020	PINK	UPS carbon neutral	1Z171WF06824495	000000171WF0450
17	11/2/2020	PINK	Fuel Surcharge	1Z171WF06824495	000000171WF0450
18	11/3/2020	PINK	Standard to Canada	1Z171WF06827164	000000171WF0450
19	11/3/2020	PINK	Residential Surcharge	1Z171WF06827164	000000171WF0450
20	11/3/2020	PINK	UPS carbon neutral	1Z171WF06827164	000000171WF0450
21	11/3/2020	PINK	Fuel Surcharge	1Z171WF06827164	000000171WF0450
22	11/9/2020	PINK	Brokerage GST	1Z171WF06827164	1432650786
23	11/9/2020	PINK	Disbursement Fee	1Z171WF06827164	1432650786
24	11/9/2020	PINK	Entry Prep Fee	1Z171WF06827164	1432650786
25	11/9/2020	PINK	QST	1Z171WF06827164	1432650786
26	11/9/2020	PINK	Pst Quebec	1Z171WF06827164	1432650786
27	11/9/2020	PINK	Customs Gst	1Z171WF06827164	1432650786
28	11/9/2020	PINK		1Z171WF06827164	1432650786
29	11/9/2020	PINK		1Z171WF06827164	1432650786
30	11/23/2020	PINK	Worldwide Expedited	1Z171WF06737259	000000171WF0480
31	11/23/2020	PINK	Peak Surcharge - Commercial	1Z171WF06737259	000000171WF0480
32	11/23/2020	PINK	UPS carbon neutral	1Z171WF06737259	000000171WF0480
33	11/23/2020	PINK	Duty and Tax Forwarding Surcharge	1Z171WF06737259	000000171WF0480
34	11/23/2020	PINK	Fuel Surcharge	1Z171WF06737259	000000171WF0480
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=TEXTJOIN(", ",1,UNIQUE(FILTER($E$8:$E$34,$D$8:$D$34=$D2,"")))

 

[DessertDiva]

@AhoyNC - I can't combine all the UPS data into one table for tracking/billing purposes. It's easier to keep them separate.

The formula you provided works perfectly. Now I just need to figure out how to get it to work across multiple tables. Thank you for your assistance.