I have two sheets, (Sheet 1) Transactions, which is like a cash register and (Sheet 2) Members, which holds a list of all members, and in the last column we need to indicate if dues are paid for 2013 by looking up in Transactions.
Transactions
A- B- C- D- E- F- G- H- I- J- K- L- M- N
Date- Receipt#- Check#- Method- RecID- First- Last- Membership Year- TeamNo- TeamCoach- Notes- TotalPaid- DepositDate- Services
4/9/2013- x- x- x- 17252- Kristopher- Bold- x- x- x- $20- x- Uniform
4/12/2013- x- x- x- 17252- Kristopher- Bold- 2013- x- x- x- $150- x- Dues
4/12/2013- x- x- x- 17249- Nicholas- Bold- 2013- x- x- x- $150- x- Dues
Members
A- B- C- D- E- F
1- SerialNo- First Name- Last Name- Zip- Phone- Dues Paid
2- 17249- Nicholas- Bold- - 2013-
3- 17252- Kristopher- Bold- - - Result should be 2013 not blank in F3
Formula in Members!F2:F3)
=IF(ISTEXT(VLOOKUP(A2,Transactions!E$2:N$2881,10,FALSE)),IF(AND(VLOOKUP(A2,Transactions!E$2:N2881,10,FALSE)="Dues",VLOOKUP(A2,Transactions!E$2:N2881,4,FALSE)=YEAR(TODAY())),VLOOKUP(A2,Transactions!E$2:N2881,4,FALSE),""),"")
Above formula works fine if member's first transaction under services is "Dues". However if first transaction is anything other than "Dues", it does not return "2013", which is what I am looking for.
Any help is appreciated.
Bookmarks