Dynamic Lookups

[hitdave85]

Name	Area 1	Area 2	Area 3	Area 4	Area 5	Area 6	Area 7	Area 8
John	Y	N	Y	Y	N	N	N	N
Brad	Y	N	N	Y	Y	N	N	N
Nate	N	N	N	Y	Y	N	N	N
Luke	N	Y	N	N	N	N	N	N
Mark	Y	N	N	N	N	N	N	N

<tbody>
</tbody>

Hello,

I am looking to drag down dynamic formulas (instead of VBA coding) which displays only results for 'Y'. The results would look like the following:

Area 1	John
Area 3	John
Area 4	John
Area 1	Brad
Area 4	Brad
Area 5	Brad
Area 4	Nate
Area 5	Nate
Area 2	Luke
Area 1	Mark

<tbody>
</tbody>

Any ideas?

Thanks!

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Area 1d>	Area 2	Area 3	Area 4	Area 5	Area 6	Area 7	Area 8		Area	Name
2	John	Y	N	Y	Y	N	N	N	N		Area 1	John
3	Brad	Y	N	N	Y	Y	N	N	N		Area 3	John
4	Nate	N	N	N	Y	Y	N	N	N		Area 4	John
5	Luke	N	Y	N	N	N	N	N	N		Area 1	Brad
6	Mark	Y	N	N	N	N	N	N	N		Area 4	Brad
7											Area 5	Brad
8											Area 4	Nate
9											Area 5	Nate
10											Area 2	Luke
11											Area 1	Mark
12

<tbody>
</tbody>

Sheet9​

Array Formulas Cell Formula K2 {=IFERROR(INDEX($B$1:$I$1,MOD(SMALL(IF($B$2:$I$6="y",ROW($B$2:$I$6)*1000+COLUMN($B$2:$I$6)),ROWS($K$2:$K2)),1000)-COLUMN($B$2)+1),"")} L2 {=IFERROR(INDEX($A$2:$A$6,SMALL(IF($B$2:$I$6="y",ROW($B$2:$I$6)-ROW($B$2)+1),ROWS($L$2:$L2))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[hitdave85]

Sweet! Works perfectly. Thank you!

 

[Eric W]

Glad to help!