Retrieve all rows of a table with data in adjacent columns while skipping ones that do not?

Question

I've been struggling to find a way to use index/match in order to return only the rows of a table that have data in corresponding adjacent columns while skipping ones that do not. I've been successful in grabbing rows that have data in one specific column - such as column 4. But I cant for the life of me figure out a way to nest an OR function or something that would look across multiple columns?

Here is an example of what I've got setup:

Data Table:

ID	X	Y	Z	P
111	2
222		5
333
444				8
555			9
666	87	1

Here is my current formula:

=INDEX(A1:E6, SMALL(IF((INDEX(A1:E6, , 2)<=9999) * (INDEX(A1:E6, , 2)>=1), MATCH(ROW(A1:E6), ROW(A1:E6)), ""), ROWS(A1:A1)),COLUMNS(B2))

This outputs:

111

666

Since its only looking in column B.

What i want instead are all of the rows instead of row 3 which is the only row without any data. It doesnt have to be via index/match!

Thank you so much!

Answer 1

If you have O365 you can use the filter/byrow/lambda/concat functions:

=FILTER(E4:I9,BYROW(F4:I9,LAMBDA(b,CONCAT(b)))<>"")