Excel 365 - Search table return true for each row

user_13445832 0
Viewed 61

TRUE & FALSE Table

enter image description here

Hey All,

I need to return ALL the rows where TRUE is found so I can put it through a FILTER function.

E.g

S2:Z2 does not contain TRUE value return FALSE for Row 2

S3:Z3 contains a TRUE value return TRUE for Row 3

Thanks!

EDIT: Example of returned data

DATA Returned in AB

enter image description here

Returned Pending

office365 spreadsheet filter excel-formula excel
3 Answers

You could use:

enter image description here

Formula in H1:

=FILTER(A1:E5,MMULT(--A1:E5,SEQUENCE(COLUMNS(A1:E5))))

NOTE: WAAR is the Dutch equivalent of TRUE and ONWAAR the equivalent to FALSE.

Or, if it's "Pending" you are looking for:

enter image description here

Formula in H1:

=INDEX({"","Pending"},1+(MMULT(--(A1:E5="Pending"),SEQUENCE(COLUMNS(A1:E5)))>0))
user_97581940

Try below formula-

=IF(SUM(--S2:Z2)=0,FALSE,TRUE)

enter image description here

EDIT: Try below formula. Here P represents Pending.

=FILTER(S2:Z6,(S2:S6="P")+(T2:T6="P")+(U2:U6="P")+(V2:V6="P")+(W2:W6="P")+(X2:X6="P")+(Y2:Y6="P")+(Z2:Z6="P"))

enter image description here

user_55147470

This is the only solution I could come up with but it seems like an awful way around it.

=IFERROR(IFS(S2:S5000="Pending", TRUE,T2:T5000="Pending", TRUE,U2:U5000="Pending", TRUE,V2:V5000="Pending", TRUE,W2:W5000="Pending", TRUE,X2:X5000="Pending", TRUE,Y2:Y5000="Pending", TRUE,Z2:Z5000="Pending", TRUE),FALSE)

Up to row 5000 being an arbitrary number that I know the data will never exceed.

user_134458320
Related