Combining two cell formulas/one with filter function

[ChristineJ]

This formula checks if the cell values in one range match those in another range and returns "yes" if true.

=IF(SUMPRODUCT(--(AL47:AL59=W1:W13)),"yes","no")

This function in cell W1 returns the values in W1:W13:

=FILTER(L32:L162,TRIM(J32:J162)="Sales")

I'd like to combine the two so no values actually have to appear in W1:W13. I'm trying the following and getting #NA.

=IF(SUMPRODUCT(--(AL47:AL59=FILTER(L32:L162,TRIM(J32:J162)="Sales"))),"yes","no")

Can this be done? Thanks.

 

[Fluff]

You will get that error if the filter doesn't return 13 items.
Try

=LET(f,FILTER(L32:L162,TRIM(J32:J162)="Sales"),IF(SUM(--(AL47:INDEX(AL47:AL100,ROWS(f))=f)),"yes","no"))

 

[ChristineJ]

You will get that error if the filter doesn't return 13 items.
Try

=LET(f,FILTER(L32:L162,TRIM(J32:J162)="Sales"),IF(SUM(--(AL47:INDEX(AL47:AL100,ROWS(f))=f)),"yes","no"))

 

[ChristineJ]

Perfect! Thanks.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[ChristineJ]

You will get that error if the filter doesn't return 13 items.
Try

=LET(f,FILTER(L32:L162,TRIM(J32:J162)="Sales"),IF(SUM(--(AL47:INDEX(AL47:AL100,ROWS(f))=f)),"yes","no"))

I had said that this code was working, but at the time I had only quickly tested it when it should have returned "yes". I'm having trouble getting it to return "no" when the values in the two ranges do not match.

I attached a sample screenshot. (This one filters on "Accounts Receivable" rather than "Sales") where the filter returns 11 rows in the range that should be matched to a user's 11 entries in AL47:AL57.

Your formula is in AQ41. Just the sum of the matches part of your formula is in AQ40, and that number does change if the entries change.

For example, if I change L41 to 5, AQ40 changes to 10 from 11, but "yes" still appears in AQ41. Likewise, if instead I change AL49 to 9.

Nothing I try changes AQ41 to "no".

This is exactly what I need to do; I just can't get it working right! Thanks.

 

[Fluff]

Missed a bit out, it should be

=LET(f,FILTER(L32:L162,TRIM(J32:J162)="Sales"),IF(SUM(--(AL47:INDEX(AL47:AL100,ROWS(f))=f))=Rows(f),"yes","no"))

 

[ChristineJ]

Missed a bit out, it should be

=LET(f,FILTER(L32:L162,TRIM(J32:J162)="Sales"),IF(SUM(--(AL47:INDEX(AL47:AL100,ROWS(f))=f))=Rows(f),"yes","no"))

Yes, that works! Thank you so much for following up. CJ

 

[Fluff]

Glad to help & thanks for the feedback.