I have a table of data where in column 1 I have connected rows 1-5 and in column 2 I have five different values belonging to the value in column 1.
Now I want to make a search function, where you can select the value from column 1 and it gives you all five rows of information belonging to that value.
Sample Sheet
However I only receive the first row of information. How am I able to get all five rows?
I am currently using the following function
=INDEX(B4:E400;MATCH(H20;B4:B400;0))
1
You may try:
<code>=filter(B4:E,scan(,B4:B,lambda(a,c,if(c="",a,c)))=H3,byrow(C4:E,lambda(Σ,counta(Σ))))
</code>
<code>=filter(B4:E,scan(,B4:B,lambda(a,c,if(c="",a,c)))=H3,byrow(C4:E,lambda(Σ,counta(Σ))))
</code>
=filter(B4:E,scan(,B4:B,lambda(a,c,if(c="",a,c)))=H3,byrow(C4:E,lambda(Σ,counta(Σ))))
6