VLOOKUP,MATCH/INDEX/TRANSPOSE/SMALL - which one?

[Carl Stephens]

Hello All,

I am looking for a formula that will look up data in cell B2, in row 4 (array is B4:AG13) and when it finds cell with the matching data to then search for the cells with X's in and then return data in column B in the same row. In the example below, the results are in cell B15:E15. Any help on this one will be appreciated. Thank you.

 

[Fluff]

How about

=FILTER(B5:B13,INDEX(C5:AG13,,XMATCH(B2,C4:AG4))="X")

 

[Carl Stephens]

How about

=FILTER(B5:B13,INDEX(C5:AG13,,XMATCH(B2,C4:AG4))="X")

Almost perfect, just the transpose function was missing, BUT....you have made my day. THANK YOU SO MUCH.

=TRANSPOSE(FILTER(B5:B13,INDEX(C5:AG13,,XMATCH(B2,C4:AG4))="X"))

 

[dreid1011]

A little late to the party it seems, but a slight variation nonetheless.

See if this does what you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2		20
3
4			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
5		A	x	x																		x
6		B
7		C			x	x
8		D								x												x
9		E					x		x
10		F											x
11		G												x								x
12		H													x	x
13		I																		x		x
14
15		A	D	G	I
Sheet4

Cell Formulas
Range	Formula
B15:E15	B15	=WRAPROWS(FILTER(B5:B13,INDEX(C5:AG13,,B2)="x"),9)
Dynamic array formulas.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Carl Stephens]

A little late to the party it seems, but a slight variation nonetheless.

See if this does what you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2		20
3
4			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
5		A	x	x																		x
6		B
7		C			x	x
8		D								x												x
9		E					x		x
10		F											x
11		G												x								x
12		H													x	x
13		I																		x		x
14
15		A	D	G	I
Sheet4

Cell Formulas
Range	Formula
B15:E15	B15	=WRAPROWS(FILTER(B5:B13,INDEX(C5:AG13,,B2)="x"),9)
Dynamic array formulas.

Your input and different way of looking at it is super appreciated still, thank you.