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

[Hari1992]

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.

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.

 

[Habtest]

Hi, try this:

Book1
	A	B	C
1
2
3	Name	BARNAMES	CARNAMES
4	Comp	BAR1	CAR1
5	Vlp	BAR2	CAR2
6	Verifications	BAR3	CAR3
7	Non	BAR4	CAR4
8
9
10
11
12
13	Comp,Vlp,Non	BAR1,BAR2,BAR4
14	Comp,Vlp,Non	CAR1,CAR2,CAR4
Sheet1

Cell Formulas
Range	Formula
B13	B13	=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14	B14	=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))

 

[Hari1992]

Hi, try this:

Book1
	A	B	C
1
2
3	Name	BARNAMES	CARNAMES
4	Comp	BAR1	CAR1
5	Vlp	BAR2	CAR2
6	Verifications	BAR3	CAR3
7	Non	BAR4	CAR4
8
9
10
11
12
13	Comp,Vlp,Non	BAR1,BAR2,BAR4
14	Comp,Vlp,Non	CAR1,CAR2,CAR4
Sheet1

Cell Formulas
Range	Formula
B13	B13	=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14	B14	=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.

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

Please suggest. Thanks in advance.

 

[Habtest]

=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.

 

[Hari1992]

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

It shows as

 

[Habtest]

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
	A	B	C
1
2
3	Name	BARNAMES	CARNAMES
4	Comp	BAR1	CAR1
5	Vlp	BAR2	CAR2
6	Verifications	BAR3	CAR3
7	Non	BAR4	CAR4
8
9
10
11
12
13	Comp,Vlp,Non	BAR1 BAR2 BAR4
14	Comp,Vlp,Non	CAR1 CAR2 CAR4
Sheet1

Cell Formulas
Range	Formula
B13	B13	=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14	B14	=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))

 

[Hari1992]

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

Sheet 1:

Service	Impact
Composite	Composite 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.
LVP	LVP 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 Verifications	Current 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
NON	NON: 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 Validation	Address 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.
Internal	Internal: will not be able to determine eligibility and/or authorize eligibility.
Remote Proofing	Remote Proofing (RDP): Users attempting to create an portal account cannot complete the remote proof .

 

[Hari1992]

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
	A	B	C
1
2
3	Name	BARNAMES	CARNAMES
4	Comp	BAR1	CAR1
5	Vlp	BAR2	CAR2
6	Verifications	BAR3	CAR3
7	Non	BAR4	CAR4
8
9
10
11
12
13	Comp,Vlp,Non	BAR1 BAR2 BAR4
14	Comp,Vlp,Non	CAR1 CAR2 CAR4
Sheet1

Cell Formulas
Range	Formula
B13	B13	=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14	B14	=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]

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).

Book1
	A	B	C
1
2
3	Name	BARNAMES	CARNAMES
4	Comp	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5	Vlp	BAR2	CAR2
6	Verifications	BAR3	CAR3
7	Non	BAR4	CAR4
8
9
10
11
12
13	Comp,Vlp,Non	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BAR2 BAR4
14	Comp,Vlp,Non	#VALUE!
Sheet2

Cell Formulas
Range	Formula
B4	B4	=REPT("-",255)
C4	C4	=REPT("-",256)
B13	B13	=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14	B14	=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
	A	B
1	Service	Impact
2	Composite	Composite
3	Composite
4	Composite	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
5	Composite	* Multiple Notes/
Sheet1

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

 

[Hari1992]

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).

Book1
	A	B	C
1
2
3	Name	BARNAMES	CARNAMES
4	Comp	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5	Vlp	BAR2	CAR2
6	Verifications	BAR3	CAR3
7	Non	BAR4	CAR4
8
9
10
11
12
13	Comp,Vlp,Non	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BAR2 BAR4
14	Comp,Vlp,Non	#VALUE!
Sheet2

Cell Formulas
Range	Formula
B4	B4	=REPT("-",255)
C4	C4	=REPT("-",256)
B13	B13	=TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))
B14	B14	=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
	A	B
1	Service	Impact
2	Composite	Composite
3	Composite
4	Composite	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
5	Composite	* 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.