Filter formula for looking up data in a table

Question

Hi, I,m looking for a formula that can lookup and return results when I select the name and return the vehicles at the top

eg - Andy Scooter Bicycle Car Bus

Thanks for any help

	Scooter	Bicycle	Motorbike	Car	Bus	Lorry	HGV
Andy	x	x		x	x
Brian	x		x			x	x
Carl		x	x	x	x		x
Dave	x	x	x

oliverscheurich · Answer

=LET(arr,LAMBDA(x,CHOOSE(x,A2:A5,SEQUENCE(ROWS(B2:H5)))),
MAP(arr({1}),arr({2}),LAMBDA(a,b,TEXTJOIN(" ",,a,FILTER(B1:H1,CHOOSEROWS(B2:H5,b)="x")))))This should work if i correctly understand what you are looking for.

harun24hr · Answer

Same as previous question answer.=TEXTJOIN(", ",1,FILTER(B1:J1,FILTER(B2:J6,A2:A6=O1)="x"))
=BYROW(A2:J6,LAMBDA(r,TEXTJOIN(", ",1,TAKE(r,,1),FILTER(B1:J1,DROP(r,,1)="x"))))&nbsp;

Forum Discussion

Filter formula for looking up data in a table

2 Replies

Resources