Search code examples
google-sheetsindexingmatchrow

Index Match Search Field with multiple rows in google sheets

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

Solution

  • You may try:

    =filter(B4:E,scan(,B4:B,lambda(a,c,if(c="",a,c)))=H3,byrow(C4:E,lambda(Σ,counta(Σ))))

    enter image description here