lookup value and return every instance of that value

philip121

New Member
Joined
May 22, 2014
Messages
13
Office Version
  1. 365
Platform
  1. MacOS
I need to lookup each value from column A in column B, and return instances of that value from column B into column C.
Example:
lookup from column A: SXWTK00022

results found in column B:
SXWTK00022-2XL
SXWTK00022-LG
SXWTK00022-MD
SXWTK00022-SM
SXWTK00022-XL

copy each result and paste in column C.

Your help is greatly appreciated.
 

Attachments

  • Screen Shot 2020-09-04 at 2.55.54 PM.png
    Screen Shot 2020-09-04 at 2.55.54 PM.png
    75.8 KB · Views: 14
  • Screen Shot 2020-09-04 at 2.55.39 PM.png
    Screen Shot 2020-09-04 at 2.55.39 PM.png
    139.9 KB · Views: 14

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Would you (a) give some examples of column C that you expect and (b) use XL2BB so we don't have to try to duplicate your data and (c) which version of Excel do you run?
 
Upvote 0
If your version has TEXTJOIN, try this in C and fill down accordingly.

Code:
=TEXTJOIN(", ",1,IF(A1=LEFT(B:B,LEN(A1)),B:B,""))

I think 2019 Excel for the MAC has TEXTJOIN

Book1
ABC
1SXWTK00022custom10SXWTK00022-2XL, SXWTK00022-LG, SXWTK00022-MD, SXWTK00022-SM, SXWTK00022-XL
2SXWTK00028custom60SXWTK00028-LG, SXWTK00028-XL
3SXTWK00030custom70 
4SXWLS00005SXWTK00022-2XLSXWLS00005-2XL, SXWLS00005-LG, SXWLS00005-MD
5SXWTK00022-LG
6SXWTK00022-MD
7SXWTK00022-SM
8SXWTK00022-XL
9SXWTK00028-LG
10SXWTK00028-XL
11SXWLS00005-2XL
12SXWLS00005-LG
13SXWLS00005-MD
Sheet2
Cell Formulas
RangeFormula
C1:C4C1=TEXTJOIN(", ",1,IF(A1=LEFT(B:B,LEN(A1)),B:B,""))
 
Upvote 0
if your Excel version contain Power Query
Sale ItemsSKUSale ItemsSKUResult.SKU
SXWTK00022CUSTOMIOSXWTK00022CUSTOMIOSXWTK00022-2XL
SXWTK00028CUSTOM60SXWTK00022CUSTOMIOSXWTK00022-LG
SXWTK00030CUSTOM70SXWTK00022CUSTOMIOSXWTK00022-MD
SXWTK00032TP00011SXWTK00022CUSTOMIOSXWTK00022-SM
BLWTS00108TP099SXWTK00022CUSTOMIOSXWTK00022-XL
BLWTS00107TP100SXWTK00028CUSTOM60
BLWTS001062020SUCKAJ01SXWTK00030CUSTOM70
BLWTS00134CHMA00114SXWTK00032TP00011
BLWTS00014CUWHS00098-2XLBLWTS00108TP099
BLWTS00124CUWHS00098-LGBLWTS00107TP100
BLWTS00065CUWHS00098-MDBLWTS001062020SUCKAJ01
BLWTSQQQ27CUWHS00098-SMBLWTS00134CHMA00114
SXMT00077-LGBLWTS00014CUWHS00098-2XL
SXMT00077-MDBLWTS00124CUWHS00098-LG
SXMT00077-SMBLWTS00065CUWHS00098-MD
SXMT00077-XLBLWTSQQQ27CUWHS00098-SM
SXMT00065-2XLSXMT00077-LG
SXMT00065-3XLSXMT00077-MD
SXMT00065-LGSXMT00077-SM
SXMT00065-MDSXMT00077-XL
SXMT00065-SMSXMT00065-2XL
SXMT00065-XLSXMT00065-3XL
SXMHA00084SXMT00065-LG
SXBH00007SXMT00065-MD
SXMTK00002-2XLSXMT00065-SM
SXMTK00002-LGSXMT00065-XL
SXMTK00002-MDSXMHA00084
SXMTK00002-SMSXBH00007
SXMTK00002-XLSXMTK00002-2XL
SXWTK00022-2XLSXMTK00002-LG
SXWTK00022-LGSXMTK00002-MD
SXWTK00022-MDSXMTK00002-SM
SXWTK00022-SMSXMTK00002-XL
SXWTK00022-XLSXWTK00022-2XL
SXWLS00005-2XLSXWTK00022-LG
SXWLS00005-LGSXWTK00022-MD
SXWLS00005-MDSXWTK00022-SM
SXWLS00005-SMSXWTK00022-XL
SXWLS00005-XLSXWLS00005-2XL
SXMHF00080-LG/XLSXWLS00005-LG
SXMHF00080-MD/LGSXWLS00005-MD
SXMHF00080-SM/MDSXWLS00005-SM
SXWLS00005-XL
SXMHF00080-LG/XL
SXMHF00080-MD/LG
SXMHF00080-SM/MD

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TBD = Table.AddColumn(Source, "TBD", each Text.BeforeDelimiter([SKU], "-"), type text),
    Join = Table.NestedJoin(Source,{"Sale Items"},TBD,{"TBD"},"Result",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Result", {"SKU"}, {"Result.SKU"})
in
    Expand
 
Upvote 0
which version of Excel do you run?
@philip121
When responding to the above please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Thanks for the reply.
I don't have Power Query but I would like my results to look exactly like this...if possible.
if your Excel version contain Power Query
Sale ItemsSKUSale ItemsSKUResult.SKU
SXWTK00022CUSTOMIOSXWTK00022CUSTOMIOSXWTK00022-2XL
SXWTK00028CUSTOM60SXWTK00022CUSTOMIOSXWTK00022-LG
SXWTK00030CUSTOM70SXWTK00022CUSTOMIOSXWTK00022-MD
SXWTK00032TP00011SXWTK00022CUSTOMIOSXWTK00022-SM
BLWTS00108TP099SXWTK00022CUSTOMIOSXWTK00022-XL
BLWTS00107TP100SXWTK00028CUSTOM60
BLWTS001062020SUCKAJ01SXWTK00030CUSTOM70
BLWTS00134CHMA00114SXWTK00032TP00011
BLWTS00014CUWHS00098-2XLBLWTS00108TP099
BLWTS00124CUWHS00098-LGBLWTS00107TP100
BLWTS00065CUWHS00098-MDBLWTS001062020SUCKAJ01
BLWTSQQQ27CUWHS00098-SMBLWTS00134CHMA00114
SXMT00077-LGBLWTS00014CUWHS00098-2XL
SXMT00077-MDBLWTS00124CUWHS00098-LG
SXMT00077-SMBLWTS00065CUWHS00098-MD
SXMT00077-XLBLWTSQQQ27CUWHS00098-SM
SXMT00065-2XLSXMT00077-LG
SXMT00065-3XLSXMT00077-MD
SXMT00065-LGSXMT00077-SM
SXMT00065-MDSXMT00077-XL
SXMT00065-SMSXMT00065-2XL
SXMT00065-XLSXMT00065-3XL
SXMHA00084SXMT00065-LG
SXBH00007SXMT00065-MD
SXMTK00002-2XLSXMT00065-SM
SXMTK00002-LGSXMT00065-XL
SXMTK00002-MDSXMHA00084
SXMTK00002-SMSXBH00007
SXMTK00002-XLSXMTK00002-2XL
SXWTK00022-2XLSXMTK00002-LG
SXWTK00022-LGSXMTK00002-MD
SXWTK00022-MDSXMTK00002-SM
SXWTK00022-SMSXMTK00002-XL
SXWTK00022-XLSXWTK00022-2XL
SXWLS00005-2XLSXWTK00022-LG
SXWLS00005-LGSXWTK00022-MD
SXWLS00005-MDSXWTK00022-SM
SXWLS00005-SMSXWTK00022-XL
SXWLS00005-XLSXWLS00005-2XL
SXMHF00080-LG/XLSXWLS00005-LG
SXMHF00080-MD/LGSXWLS00005-MD
SXMHF00080-SM/MDSXWLS00005-SM
SXWLS00005-XL
SXMHF00080-LG/XL
SXMHF00080-MD/LG
SXMHF00080-SM/MD

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TBD = Table.AddColumn(Source, "TBD", each Text.BeforeDelimiter([SKU], "-"), type text),
    Join = Table.NestedJoin(Source,{"Sale Items"},TBD,{"TBD"},"Result",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Result", {"SKU"}, {"Result.SKU"})
in
    Expand




Thanks for the reply.
I don't have Power Query but I would like my results to look exactly like this...if possible.
 
Upvote 0
Office 365 on Mac
Thanks. next question is: Do you have the FILTER function in your Excel 365 (not everybody has)?
If so, would something like this be what you want?
Formula only needs to go in a single cell & the other results will 'spill' to the required cells.
Adjust ranges of course.

20 09 05.xlsm
ABCD
1Sale itemsSKULookup ValueSKU
2SXWTK00022custom10SXWTK00022SXWTK00022-2XL
3SXWTK00028custom60SXWTK00022-LG
4SXTWK00030custom70SXWTK00022-MD
5SXWLS00005SXWTK00022-2XLSXWTK00022-SM
6SXWTK00022-LGSXWTK00022-XL
7SXWTK00022-MD
8SXWTK00022-SM
9SXWTK00022-XL
10SXWTK00028-LG
11SXWTK00028-XL
12SXWLS00005-2XL
13SXWLS00005-LG
14SXWLS00005-MD
15
Lookup SKUs
Cell Formulas
RangeFormula
D2:D6D2=FILTER(B2:B20,LEFT(B2:B20,LEN(C2))=C2)
Dynamic array formulas.
 
Upvote 0
Thanks Peter,
Yes, I have the Filter function and this works nicely.
However, is there a way to continue down the list of lookup values and continue pasting the results down column D in the next available cells?


Sale Itemskulookup itemresults
CHWT00036CUWD00007-LGCHWT00036CHWT00036-2XL
SXWTK00031CUWD00007-MDSXWTK00031CHWT00036-LG
SXWTK00022CUWD00007-SMSXWTK00022CHWT00036-MD
SXWTK00028CUWD00007-XLSXWTK00028CHWT00036-SM
SXWTK00030CUWD00007-XSSXWTK00030CHWT00036-XL
SXWTK00032CUMHA00432SXWTK00032
BLWTS00108CUMT00455-2XLBLWTS00108
BLWTS00107CUMT00455-3XLBLWTS00107
BLWTS00106CUMT00455-4XLBLWTS00106
BLWTS00134CUMT00455-LGBLWTS00134
BLWTS00014CUMT00455-MDBLWTS00014
BLWTS00124CUMT00455-SMBLWTS00124
BLWTS00065CUMT00455-XLBLWTS00065
BLWTS00027CUHD00036BLWTS00027
BLWTS00088CUKC00012BLWTS00088
BLWTS00097CUSD00050BLWTS00097
BLWTS00120CUIA00005BLWTS00120
BLWTS00038CUMCJ00017-05BLWTS00038
BLWTS00137CUMCJ00017-03BLWTS00137
BLWTS00093CUMCJ00017-02BLWTS00093
BLWTS00132CUMCJ00017-01BLWTS00132
BLWTS00075CUMCJ00017-04BLWTS00075
CUWTS00313CUMHA00406CUWTS00313
CUWTS00296CUFT00078-LGCUWTS00296
CUWTS00378STMT001-2XLCUWTS00378
buwts00041STMT001-LGbuwts00041
CHWT00001STMT001-MDCHWT00001
BUWTS00067STMT001-SMBUWTS00067
CHWT00003STMT001-XLCHWT00003
CHWT00011CUMLS00031-2XLCHWT00011
CHWT00015CUMLS00031-LGCHWT00015
CHWT00029CUMLS00031-MDCHWT00029
CHWT00030CUMLS00031-SMCHWT00030
CHWT00005CUMLS00031-XLCHWT00005
CHWTK00011CUMCS00023-2XLCHWTK00011
CHWT00034CUMCS00023-LGCHWT00034
BLWTS00075CUMCS00023-MDBLWTS00075
BLWTS00132CUMCS00023-SMBLWTS00132
BLWTS00066CUMCS00023-XLBLWTS00066
SXWTS00070CUWTK00138-2XLSXWTS00070
SXWTS00028CUWTK00138-LGSXWTS00028
CUWTS00326CUWTK00138-MDCUWTS00326
CUWTS00322CUWTK00138-SMCUWTS00322
BUWTS00050CUWTK00138-XLBUWTS00050
BUWTS00080CUWTK00138-XSBUWTS00080
buwts00053CUSA00039buwts00053
BUWTS00066CUSA00084BUWTS00066
buwts00043CUMP00042-2XLbuwts00043
BUWTS00078CUMP00042-LGBUWTS00078
BUWTS00082CUMP00042-MDBUWTS00082
BUWTS00072CUMP00042-SMBUWTS00072
CHWT00043CUMP00042-XLCHWT00043
BLWTS00117CUMP00026-05BLWTS00117
BLWTS00118CUMP00026-3XLBLWTS00118
BLWTS00065CUMP00026-03BLWTS00065
CUWTS00306CUMP00026-02CUWTS00306
CUWTS00349CUMP00026-01CUWTS00349
CUW34T00026CUMP00026-04CUW34T00026
BUWTS00049CU2016POSTMP003-2XLBUWTS00049
BUWTS00063CU2016POSTMP003-3XLBUWTS00063
BUWTS00069CU2016POSTMP003-LGBUWTS00069
BUWTS00078CU2016POSTMP003-MDBUWTS00078
BUWTS00075CU2016POSTMP003-SMBUWTS00075
BUWTS00060CU2016POSTMP003-XLBUWTS00060
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top