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

#### DessertDiva

##### New Member
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.

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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### AhoyNC

##### Well-known Member
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
ABCDEFGHI
1Order #Order DateWarehouseTracking #Carrier FeeIs tracking # on Nov UPS?Is tracking # on Dec UPS?Is tracking # accounted for?UPS Invoice #
2826310/31/2020Pink1Z171WF0682449527.74TRUEFALSETRUE1431875072, 000000171WF0450
3827711/2/2020Pink1Z171WF0682716422.31TRUEFALSETRUE000000171WF0450, 1432650786
4847611/19/2020Pink1Z171WF0673725936.64TRUETRUETRUE000000171WF0480
5
6Here's Table 2 (November UPS):
7Order DateWarehouseShip Service Charge DescriptionTracking #UPS Invoice #
811/4/2020PINKEntry Prep Fee1Z171WF068244951431875072
911/4/2020PINKBrokerage GST1Z171WF068244951431875072
1011/4/2020PINKDisbursement Fee1Z171WF068244951431875072
1111/4/2020PINKCa British Columbia Pst1Z171WF068244951431875072
1211/4/2020PINKCustoms Gst1Z171WF068244951431875072
1311/4/2020PINK1Z171WF068244951431875072
1411/4/2020PINK1Z171WF068244951431875072
1611/2/2020PINKUPS carbon neutral1Z171WF06824495000000171WF0450
1711/2/2020PINKFuel Surcharge1Z171WF06824495000000171WF0450
1911/3/2020PINKResidential Surcharge1Z171WF06827164000000171WF0450
2011/3/2020PINKUPS carbon neutral1Z171WF06827164000000171WF0450
2111/3/2020PINKFuel Surcharge1Z171WF06827164000000171WF0450
2211/9/2020PINKBrokerage GST1Z171WF068271641432650786
2311/9/2020PINKDisbursement Fee1Z171WF068271641432650786
2411/9/2020PINKEntry Prep Fee1Z171WF068271641432650786
2511/9/2020PINKQST1Z171WF068271641432650786
2611/9/2020PINKPst Quebec1Z171WF068271641432650786
2711/9/2020PINKCustoms Gst1Z171WF068271641432650786
2811/9/2020PINK1Z171WF068271641432650786
2911/9/2020PINK1Z171WF068271641432650786
3011/23/2020PINKWorldwide Expedited1Z171WF06737259000000171WF0480
3111/23/2020PINKPeak Surcharge - Commercial1Z171WF06737259000000171WF0480
3211/23/2020PINKUPS carbon neutral1Z171WF06737259000000171WF0480
3311/23/2020PINKDuty and Tax Forwarding Surcharge1Z171WF06737259000000171WF0480
3411/23/2020PINKFuel Surcharge1Z171WF06737259000000171WF0480
Sheet1
Cell Formulas
RangeFormula
I2:I4I2=TEXTJOIN(", ",1,UNIQUE(FILTER(\$E\$8:\$E\$34,\$D\$8:\$D\$34=\$D2,"")))

#### DessertDiva

##### New Member
@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.

Replies
18
Views
501
Replies
9
Views
215
Replies
5
Views
333
Replies
1
Views
471
Replies
1
Views
153

1,127,595
Messages
5,625,697
Members
416,128
Latest member
WarJamAnd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back