Dynamic Lookup for multiple values in a cell (comma separated) and return the corresponding ID to a single cell (comma separated also)

gtgaabaron

New Member
Joined
May 25, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello, I looking for a solution to a similar problem reported here (Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)).
The thing is not always the amount of values (IDs) will be the same within each cell (at least 1, max=several) that's why the fixed version of using concatenated vlookup+left/mid/right will not work for me and also I'm not able to install any add-in (as mentioned using Morefunc). The only fixed size is the size of the values to lookup (IDs - in green), 8 characters (letters+numbers).

I'm not sure but, is it possible to setup a loop within excel formulas/functions ?
Below is a table containing an example of the issue I'm trying to resolve and the expected values (tables are in different tab). Hope you can help.
Thanks.

tables.jpg
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,138
Office Version
  1. 2019
Platform
  1. Windows
Here is a formula based approach that relies on two sets of helper cells to split the comma-separated data into parts that can be matched in the main data table using INDEX/MATCH. This is done for both the IDv2 data and the Product data. The results are then recombined using TEXTJOIN. The helper columns accommodate up to 7 items in the original CSV string, but can easily be expanded if necessary. The helper columns can be positioned far to the right or even hidden.
Book3
ABCDEFGHIJKLMNO
3IDsProductExpected Results12345671234
4OP512E08,OP513N16Product55,Product61FL54Y94,FL83G84FL54Y94FL83G84     Product55Product61  
5OP435R84,OP647C84,OP747B38Product31,Product48,Product19FL32G78,FL74L45,FL34P45FL32G78FL74L45FL34P45    Product31Product48Product19 
6OP121F45,OP832J43,OP212P67,OP495G74Product43,Product22,Product74,Product23FL21A24,FL09S54,FL75D34,FL24Q92FL21A24FL09S54FL75D34FL24Q92   Product43Product22Product74Product23
7
8
9
10IDsProductIDv2
11OP512E08Product55FL54Y94
12OP513N16Product61FL83G84
13OP435R84Product31FL32G78
14OP647C84Product48FL74L45
15OP747B38Product19FL34P45
16OP121F45Product43FL21A24
17OP832J43Product22FL09S54
18OP212P67Product74FL75D34
19OP495G74Product23FL24Q92
20
gtgaabaron
Cell Formulas
RangeFormula
B4:B6B4=TEXTJOIN(",",1,L4:R4)
C4:C6C4=TEXTJOIN(",",1,E4:K4)
E4:K6E4=IFERROR(INDEX(Table2[IDv2],MATCH(MID($A4,(E$3*9)-8,8),Table2[IDs],0)),"")
L4:O6L4=IFERROR(INDEX(Table2[Product],MATCH(MID($A4,(L$3*9)-8,8),Table2[IDs],0)),"")
 

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
Enter below Array formula (Ctrl+Shift+Enter) in cell B4 and copy to cell C4 and then cpy down:

=TEXTJOIN(",",,IF(ISNUMBER(MATCH($A$11:$A$50,TRIM(MID(SUBSTITUTE($A4,",",REPT(" ",LEN($A4))),(ROW($1:$255)-1)*LEN($A4)+1,LEN($A4))),0)),B$11:B$50,""))


This considers only column A values (A11:A50) for the multiple comma separated cell A4 values and returns the corresponding column B values (B11:B50) in cell B4 and corresponding column C values (C11:C50) in cell C4.


Regards,
Amit Tandon
 

Attachments

  • MultipleMatchingValues_SingleCell.gif
    MultipleMatchingValues_SingleCell.gif
    41 KB · Views: 58
Last edited by a moderator:

Forum statistics

Threads
1,136,202
Messages
5,674,387
Members
419,505
Latest member
Mpatsonjoka

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
Top