if-statement google-sheets lambda google-sheets-formula max

How do I amend my IF AND statement to correctly show payments made or not?

I've been trying to set up a formula to show if someone has paid their membership fees. They can either pay yearly or monthly. The yearly formula seems to be working but the monthly formula seems to not work. Basically, I want it to show - if the month referenced is either the current month or a later month - if the person named for that row has paid for that month. If possible, I also want to show if someone has said they've paid but the payment isn't showing in the bank.

This is the 'Income - Membership fees' sheet referenced in the formula.

Image of 'Income - Membership fees' sheet with multiple columns including C:'Player name', D:'Type' (type of membership - either monthly or yearly), E:'Payment month (if applicable)', and G:'Showing in bank'

And this is the main sheet and the formula:

=IF(AND('Income - Membership fees'!$C:$C = $A2, E$1 < TODAY(),'Income - Membership fees'!$E:$E = E$1, $C2 = "Monthly",'Income - Membership fees'!$G:$G = "Yes"),"PAID", IF(AND('Income - Membership fees'!$C:$C = $A2, E$1 < TODAY(),'Income - Membership fees'!$E:$E = E$1, $C2 = "Monthly",'Income - Membership fees'!$G:$G <> "Yes"),"NOT IN BANK", IF(OR(E$1 > TODAY(),$C2 <> "Monthly"),"","NOT PAID")))

Image of main sheet with columns including A:'Player name', C:'Payment type' (type of membership - either monthly or yearly), D:'Yearly 2024-25', E to L: Various months of the year

I've tried using vlookup, and * instead of AND. I've also tried looking into byrow, lambda, and match but I don't know if they're the right thing for me to use.

Solution

use:

=INDEX(LET(a, A2:INDEX(A:A, MAX(ROW(A:A)*(A:A<>""))),    b, OFFSET(a,,1), 
           d, D1:INDEX(1:1,,MAX(COLUMN(1:1)*(1:1<>""))), c, 'Income - Membership fees'!C:C, 
 IF(IFNA(VLOOKUP(a&b&"Yes"&d, {c&OFFSET(c,,1)&OFFSET(c,,4)&OFFSET(c,,2)}, 1,))<>"","PAID", 
 IF(IFNA(VLOOKUP(a&b&"No"&d,  {c&OFFSET(c,,1)&OFFSET(c,,4)&OFFSET(c,,2)}, 1,))<>"","NOT IN BANK", 
 IF((b<>"Monthly")+(d>TODAY()),,"NOT PAID")))))