How to return multiple lookup values without using FILTER formula

arun_eagertolearn said:

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,"")

Click to expand...