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

Hari1992

New Member
Joined
May 7, 2021
Messages
11
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi,

I am looking for formula in excel for Dynamic Lookup for multiple values in a cell (comma separated) and return the corresponding values to a single cell (comma separated also). Here is the example.

1620372949179.png

90
Here, I have an input table in sheet 1 and I am looking for formula to get the output as highlighted (yellow) in sheet 2 but taking reference as A13 and A14 in sheet 2 (which may varies based on the dynamic multiple drop down selection). Please suggest.

Thanks in Advance.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
166
Office Version
  1. 365
Platform
  1. Windows
Hi, try this:
Book1
ABC
1
2
3NameBARNAMESCARNAMES
4CompBAR1CAR1
5VlpBAR2CAR2
6VerificationsBAR3CAR3
7NonBAR4CAR4
8
9
10
11
12
13Comp,Vlp,NonBAR1,BAR2,BAR4
14Comp,Vlp,NonCAR1,CAR2,CAR4
Sheet1
Cell Formulas
RangeFormula
B13B13=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14B14=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))
 
Solution

Hari1992

New Member
Joined
May 7, 2021
Messages
11
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi, try this:
Book1
ABC
1
2
3NameBARNAMESCARNAMES
4CompBAR1CAR1
5VlpBAR2CAR2
6VerificationsBAR3CAR3
7NonBAR4CAR4
8
9
10
11
12
13Comp,Vlp,NonBAR1,BAR2,BAR4
14Comp,Vlp,NonCAR1,CAR2,CAR4
Sheet1
Cell Formulas
RangeFormula
B13B13=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14B14=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))
Thank you very much, the formula worked for the logic provided above.

I am trying using the same logic, by having the source in sheet1 and destination in sheet2 and Its not working here.

=TEXTJOIN(",",TRUE,IF(ISNUMBER(FIND(","&Sheet1!$A$2:$A$32&",",","&$F$25:$P$26&",")),Sheet2!$B$2:$B$32,""))

Here, is the sheet2... and I need to fill the Impact section by using the service section.

1620377752989.png


I have provided the sheet1 above in my main question. I am looking for Impact section like this.

1620378197030.png


Please suggest. Thanks in advance.
 

Attachments

  • 1620377555953.png
    1620377555953.png
    37.7 KB · Views: 2

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
166
Office Version
  1. 365
Platform
  1. Windows
=TEXTJOIN(",",TRUE,IF(ISNUMBER(FIND(","&Sheet1!$A$2:$A$32&",",","&$F$25:$P$26&",")),Sheet2!$B$2:$B$32,""))
Is F25:P26 in sheet2 merged? Try remove the bold part, :$P$26

Also, if you'd like to display the results by separate line in one cell, you can change the TEXTJOIN(","... part to TEXTJOIN(CHAR(10)... then allow Wrap text in cell format.
 
Last edited:

Hari1992

New Member
Joined
May 7, 2021
Messages
11
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Tied this
=TEXTJOIN(CHAR(10),TRUE,IF(ISNUMBER(FIND(","&Interface!$A$2:$A$32&",",","&$F$25&",")),Interface!$B$2:$B$32,""))

It shows as
1620401136623.png
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
166
Office Version
  1. 365
Platform
  1. Windows
Do you mind upload a sample of original data by XL2BB? The formula should work as it did in the mock data. I guess it is because format or space but cannot be sure without looking at the sheets.

Test.xlsx
ABC
1
2
3NameBARNAMESCARNAMES
4CompBAR1CAR1
5VlpBAR2CAR2
6VerificationsBAR3CAR3
7NonBAR4CAR4
8
9
10
11
12
13Comp,Vlp,NonBAR1 BAR2 BAR4
14Comp,Vlp,NonCAR1 CAR2 CAR4
Sheet1
Cell Formulas
RangeFormula
B13B13=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14B14=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))
 
Last edited:

Hari1992

New Member
Joined
May 7, 2021
Messages
11
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Yes, the following is the data that I am working on fixing the issue. Based on the selection of service section, I want to populate the Impact section. I should also be able to populate data based on the multiple selections like the above mock data. With the formula you have provided, I was able to populate data that has a single line, but was not working for the section which has multiple lines. Please suggest.

Sheet 2
1620667275337.png


Sheet 1:
ServiceImpact
CompositeComposite

Scraps is an application for those who like to take quick notes. With this app you can scribble what's on your mind and then forget it. Just write with your fingers or a stylus and it is permanently there till
* Multiple Notes/
* No need to press save (Auto save onPause)
* Multiple Colour Options
* Multiple Brush Sizes. Change Brush Size using Volume Up/Down keys.

Scraps is an application for those who like to take quick notes. With this app you can scribble what's on your mind and then forget it. Just write with your fingers or a stylus and it is permanently there till you clear it.Press back button or home key wont delete it :).
* Multiple Colour Options
* Multiple Brush Sizes. Change Brush Size using Volume Up/Down keys.
LVPLVP

Scraps is an application for those who like to take quick notes. With this app you can scribble what's on your mind and then forget it. Just write with your fingers or a stylus and it is permanently there till
* Multiple Notes/
* No need to press save (Auto save onPause) Multiple Colour Options
* Multiple Brush Sizes. Change Brush Size using Volume Up/Down keys.
•If a successful call is made, the case will be updated accordingly.
Current VerificationsCurrent Verifications:

Scraps is an application for those who like to take quick notes. With this app you can scribble what's on your mind and then forget it. Just write with your fingers or a stylus and it is permanently there till Multiple Notes/

They will attempt to call three additional times, once every six hours. If a successful call is made, the case will be updated accordingly. If all three calls are unsuccessful, an RFI will be generated
NONNON:

If the non-SEC is triggered, coverage will not be able to be verified. EBOUH has the best of it.

The system will attempt to call SEC three additional times, once every 4 mins.
•If a successful call is made, the case will be updated accordingly.
•If all three calls are unsuccessful, an will be generated for.
Address ValidationAddress Validation:

Address entered in will not be validated. This might result in notices being sent to incorrect address. In addition, might not have correct address of the.
InternalInternal:

will not be able to determine eligibility and/or authorize eligibility.
Remote ProofingRemote Proofing (RDP):

Users attempting to create an portal account cannot complete the remote proof .
 

Attachments

  • 1620666069284.png
    1620666069284.png
    13.1 KB · Views: 2

Hari1992

New Member
Joined
May 7, 2021
Messages
11
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Do you mind upload a sample of original data by XL2BB? The formula should work as it did in the mock data. I guess it is because format or space but cannot be sure without looking at the sheets.

Test.xlsx
ABC
1
2
3NameBARNAMESCARNAMES
4CompBAR1CAR1
5VlpBAR2CAR2
6VerificationsBAR3CAR3
7NonBAR4CAR4
8
9
10
11
12
13Comp,Vlp,NonBAR1 BAR2 BAR4
14Comp,Vlp,NonCAR1 CAR2 CAR4
Sheet1
Cell Formulas
RangeFormula
B13B13=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14B14=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))

I tried to add XL2BB in my excel but it was getting stuck, so had to paste the table here. Please let me kniw if you need more details
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
166
Office Version
  1. 365
Platform
  1. Windows
After some testing, the reason for the error should be the text length limit. Excel apparently cannot handle a cell with over 255 characters in calculation (see below, the function works until text length exceeds 255).
Cell Formulas
RangeFormula
B4B4=REPT("-",255)
C4C4=REPT("-",256)
B13B13=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14B14=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))


Unfortunately I don't have an easy solution to that, for I don't know what, or if any, function is insusceptible to this limit. One workaround is to break each large cell in Sheet 1 down to smaller ones (<=255 charaters) and paste the Service Type before each (e.g. below), though feasiability could be an issue.
Book1
AB
1ServiceImpact
2CompositeComposite
3Composite
4CompositeScraps is an application for those who like to take quick notes. With this app you can scribble what's on your mind and then forget it. Just write with your fingers or a stylus and it is permanently there till
5Composite* Multiple Notes/
Sheet1

Or I assume other members in the forum can solve it by Macro.
 

Hari1992

New Member
Joined
May 7, 2021
Messages
11
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
After some testing, the reason for the error should be the text length limit. Excel apparently cannot handle a cell with over 255 characters in calculation (see below, the function works until text length exceeds 255).
Cell Formulas
RangeFormula
B4B4=REPT("-",255)
C4C4=REPT("-",256)
B13B13=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14B14=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))


Unfortunately I don't have an easy solution to that, for I don't know what, or if any, function is insusceptible to this limit. One workaround is to break each large cell in Sheet 1 down to smaller ones (<=255 charaters) and paste the Service Type before each (e.g. below), though feasiability could be an issue.
Book1
AB
1ServiceImpact
2CompositeComposite
3Composite
4CompositeScraps is an application for those who like to take quick notes. With this app you can scribble what's on your mind and then forget it. Just write with your fingers or a stylus and it is permanently there till
5Composite* Multiple Notes/
Sheet1

Or I assume other members in the forum can solve it by Macro.
Thanks, What if I want to add 2 lines of space between. Here Char(10) would give me 1 line. I am looking for a two-line space for a clear view.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,708
Messages
5,654,854
Members
418,155
Latest member
demasisi

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