How to return multiple lookup values without using FILTER formula

[icytuvi]

Hi,

I have the data table as below and I would like to list out names of states matching "up" or "down" value.

Filter formula does not work in my excel version so I would like to seek your help with another alternative formula that works.

Thank you very much.

Sample.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
11		Product 1	Product 2	Product 3	Product 4	Product 5	Product 6	Product 7	Product 8			up	down
12	California	-	down	-	down	-	down	-	down		Product 1	Florida, Hawaii, Idaho	Iowa, Kansas, Maine
13	Colorado	-	up	down	-	-	-	-	up		Product 2
14	Florida	up	-	-	up	up	-	-	-		Product 3
15	Hawaii	up	down	up	-	-	-	up	-		Product 4
16	Idaho	up	-	-	up	down	-	-	-		Product 5
17	Iowa	down	up		-	-	-	down	-		Product 6
18	Kansas	down	-	-	-	up	-	-	down		Product 7
19	Maine	down	-	-	down	-	-	-	up		Product 8
20	Maryland	-	down	-	-	-	-	-	-
Sheet1

 

[arun_eagertolearn]

Hi

Please check this- this is for Product 1 to Product 3, and could be extended for n number of products

Made some dummy (helper columns)- F:H (3 columns for 3 products)- for UP, and J:L for DOWN

These sections could be kept as separate sheets for ease of understanding later on

Wherever match is made, the dummy columns would return the states

Concatenating these states and removing leading blanks and trailing commas would lead to the final result

HLOOKUP of this would yield in the result in the fashion you require

Please do let know if this has been of help, thanks

Weekdays.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Prod 1	Prod 2	Prod 3		Prod 1 Up	Prod 2 Up	Prod 3 Up		Prod 1 Down	Prod 2 Down	Prod 3 Down			up	down
2	California	-	down	-										Prod 1	Florida, Hawaii, Idaho	Florida, Hawaii, Idaho
3	Colorado	-	up	down			Colorado,				Colorado,			Prod 2	Colorado, Iowa	Colorado, Iowa
4	Florida	up	-	-		Florida,				Florida,				Prod 3	Hawaii	Hawaii
5	Hawaii	up	down	up		Hawaii,		Hawaii,		Hawaii,		Hawaii,
6	Idaho	up	-	-		Idaho,				Idaho,
7	Iowa	down	up	-			Iowa,				Iowa,
8	Kansas	down	-	-
9	Maine	down	-
10	Maryland	-	down
11						Prod 1	Prod 2	Prod 3		Prod 1	Prod 2	Prod 3
12						Florida, Hawaii, Idaho,	Colorado, Iowa,	Hawaii,		Florida, Hawaii, Idaho,	Colorado, Iowa,	Hawaii,
13						Florida, Hawaii, Idaho,	Colorado, Iowa,	Hawaii,		Florida, Hawaii, Idaho,	Colorado, Iowa,	Hawaii,
14						Florida, Hawaii, Idaho	Colorado, Iowa	Hawaii		Florida, Hawaii, Idaho	Colorado, Iowa	Hawaii
Sheet2

Cell Formulas
Range	Formula
F2:H10	F2	=IF(B2="Up"," "&$A2&",","")
J2:L10	J2	=IF(B2="Up"," "&$A2&",","")
O2:O4	O2	=HLOOKUP($N2,$F$11:$H$14,4,0)
P2:P4	P2	=HLOOKUP($N2,$J$11:$L$14,4,0)
F12:H12,J12:L12	F12	=CONCAT(F2:F10)
F13:H13,J13:L13	F13	=REPLACE(F12,1,1,"")
F14:H14,J14:L14	F14	=REPLACE(F13,LEN(F13),1,"")

 

[Cubist]

@arun_eagertolearn
CONCAT() wasn't available before Excel 2016.

 

[arun_eagertolearn]

@Cubist- thanks for pointing out
Instead of concat(f2:f10), we may use f2&f3&f4&f5&f6&f7&f8&f9&f10

Thanks

 

[icytuvi]

Hi

Please check this- this is for Product 1 to Product 3, and could be extended for n number of products

Made some dummy (helper columns)- F:H (3 columns for 3 products)- for UP, and J:L for DOWN

These sections could be kept as separate sheets for ease of understanding later on

Wherever match is made, the dummy columns would return the states

Concatenating these states and removing leading blanks and trailing commas would lead to the final result

HLOOKUP of this would yield in the result in the fashion you require

Please do let know if this has been of help, thanks

Weekdays.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Prod 1	Prod 2	Prod 3		Prod 1 Up	Prod 2 Up	Prod 3 Up		Prod 1 Down	Prod 2 Down	Prod 3 Down			up	down
2	California	-	down	-										Prod 1	Florida, Hawaii, Idaho	Florida, Hawaii, Idaho
3	Colorado	-	up	down			Colorado,				Colorado,			Prod 2	Colorado, Iowa	Colorado, Iowa
4	Florida	up	-	-		Florida,				Florida,				Prod 3	Hawaii	Hawaii
5	Hawaii	up	down	up		Hawaii,		Hawaii,		Hawaii,		Hawaii,
6	Idaho	up	-	-		Idaho,				Idaho,
7	Iowa	down	up	-			Iowa,				Iowa,
8	Kansas	down	-	-
9	Maine	down	-
10	Maryland	-	down
11						Prod 1	Prod 2	Prod 3		Prod 1	Prod 2	Prod 3
12						Florida, Hawaii, Idaho,	Colorado, Iowa,	Hawaii,		Florida, Hawaii, Idaho,	Colorado, Iowa,	Hawaii,
13						Florida, Hawaii, Idaho,	Colorado, Iowa,	Hawaii,		Florida, Hawaii, Idaho,	Colorado, Iowa,	Hawaii,
14						Florida, Hawaii, Idaho	Colorado, Iowa	Hawaii		Florida, Hawaii, Idaho	Colorado, Iowa	Hawaii
Sheet2

Cell Formulas
Range	Formula
F2:H10	F2	=IF(B2="Up"," "&$A2&",","")
J2:L10	J2	=IF(B2="Up"," "&$A2&",","")
O2:O4	O2	=HLOOKUP($N2,$F$11:$H$14,4,0)
P2:P4	P2	=HLOOKUP($N2,$J$11:$L$14,4,0)
F12:H12,J12:L12	F12	=CONCAT(F2:F10)
F13:H13,J13:L13	F13	=REPLACE(F12,1,1,"")
F14:H14,J14:L14	F14	=REPLACE(F13,LEN(F13),1,"")

Thank you very much for your solution. I've been waiting for a few days for the answer and thought that this post would sink, so in the mean time, I tried looking for solution on Google and it came out with a two step solution by first listing out all the matching values by using INDEX-AGGREGATE and secondly to join those texts together (I had a later thread on this forum too seeking for help on joining text :D) I'll try with your suggestion