Problems adding a "refer to text in cell" query to a working nested if-and formula

Good morning

As my garbled title suggests, I am having some trouble with a nested IF formula.

I have created a spreadsheet sheet to assess pension scheme members individual data and categorise accordingly. The IF formula references the individual's date of birth and annual earnings to provides the result as the category of member, as below.

=CONCATENATE(IF(AND(F37>9999,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER - Aged 22-65 with qualifying earnings over £9999",""),
IF(AND(F37<5772,E37>$F$10,E37<$F$9),"ENTITLED WORKER - Aged 16-74 with qualifying earnings below £5772",""),
IF(AND(F37>9999,E37<$F$9,E37>$F$8),"NON ELIGIBLE JOBHOLDER - Aged 16-21 with qualifying earnings above £9999",""),
IF(AND(F37>9999,E37<$F$7,E37>$F$10),"NON ELIGIBLE JOBHOLDER - Aged 65-74 with qualifying earnings above £9999",""),
IF(AND(F37<10000,F37>5771,E37<$F$9,E37>$F$10),"NON ELIGIBLE JOBHOLDER - Aged 16-74 with qualifying earnings £5772-£9999",""))

I now want to expand this formula to refer to whether the individual is weekly, monthly, quarterly paid. I have created a drop down box in another cell to allow the user to select the pay frequency and wanted to add another query to the formula to refer to the text in the pay frequency cell.

I tried to create additional queries for each of the 5 above, changing the earnings figure to account for the fact that the member is weekly,monthly,quarterly...paid. An example of my attempted formula for the first of the above queries is shown below (cell D5 being the selected payment frequency):

=CONCATENATE(IF(AND(D5=”Annually”,F37>=$G$33,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER - Aged 22-65 with qualifying earnings over £9999",""),
IF(AND(D5=”Bi-annually”,F37>=$G$32,E37>$F$7,E37<$F$8,"ELIGIBLE JOBHOLDER - Aged 22-65 with Qualifying Earnings Over £4,999",""),
IF(AND(D5="Quarterly",F37>=$g$31,E37>$F$7,E37<$F$8,”ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £2499”,””),
IF(AND(D5="Monthly”,F37>=$g$30,E37>$F$7,E37<$F$8,”ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £832”,””),
IF(AND(D5="4 weekly",F37>=$g$29,E37>$F$7,E37<$F$8,”ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £768”,””),
IF(AND(D5="Fortnightly”,F37>=$g$28,E37>$F$7,E37<$F$8,”ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £384”,””),
IF(AND(D5="Weekly*,F37>=$g$27,E37>$F$7,E37<$F$8,”ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £191”,””),

I am not able to get this to work, or savy enough to identify my shortfalls on this issue.

Any assistance would be greatly appreciated.

Thanks,

Stewart

Hi Stewart

You are missing the closing parentheses on your and functions

Try:
=CONCATENATE(IF(AND(D5="Annually",F37>=$G$33,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER - Aged 22-65 with qualifying earnings over £9999",""),
IF(AND(D5="Bi-annually",F37>=$G$32,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER - Aged 22-65 with Qualifying Earnings Over £4,999",""),
IF(AND(D5="Quarterly",F37>=$G$31,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £2499",""),
IF(AND(D5="Monthly",F37>=$G$30,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £832",""),
IF(AND(D5="4 weekly",F37>=$G$29,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £768",""),
IF(AND(D5="Fortnightly",F37>=$G$28,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £384",""),
IF(AND(D5="Weekly",F37>=$G$27,E37>$F$7,E37<$F$8),"ELIGIBLE JOBHOLDER – Aged 22-65 with Qualifying Earnings Over £191",""))

Windy

Thank you so much, spreadsheet now functional. Apologies if this was a daft question.

Stewart

No problem, sometimes you cannot see the wood for the trees.

You also had a * instead of "".
And some of your double quotes look different weather this made a difference I don't know

Windy