IF AND statement help

[nnvjac]

I have current formulas of 

=IFS(F3>6000,"Jrny",F3>4500,"4th",F3>3000,"3rd",F3>1500,"2nd",F3<1500,"1st")

=IFS(F527>4000,"Jrny",F527>3000,"4th",F527>2000,"3rd",F527>1000,"2nd",F527<1000,"1st")

In column D I list a branch of either CO, G&P, ST, LT, HDR

Each branch has different work hours.  Instead of changing my formula based on what is in column D I would like to do a multiple if and statement.

So it would say if D3="G&P" and F3>6000, the result should be "Jrny" or if D3="LT" and F3>4000 the result should be "Jrny"

Column F shows total on the job hours earned.  There are 4 periods and the period the person is at is based on completing 1/4 of the required work hours each period.

CO and GP branches have same hours, HDR has hours of 8000, 6000, 4000, 2000 and ST and LT are 4000, 3000, 2000, 1000

I tried this:

=

IF(AND(D3=”CO”,F3>6000),"Jrny",

IF(AND(D3=”CO”,F3>4500,)"4th",

IF(AND(D3=”CO”,F3>3000),"3rd",

IF(AND(D3=”CO”,F3>1500),"2nd",

IF(AND(D3=”CO”,F3<1500),"1st",

IF(AND(D3=”LT”,F3>4000),"Jrny",

IF(AND(D3=”LT”,F3>3000),"4th",

IF(AND(D3=”LT”,F3>2000),"3rd",

IF(AND(D3=”LT”,F3>1000),"2nd",

IF(AND(D3=”LT”,F3<1000),"1st")))))))))))

The above didn't work.

How would I write one formula that can be used and will base the answer on what is in Column D and in Column F?

[#2]

@nnvjac 

For example

=IFS(D3="HDR", IFS(F3>8000,"Jrny",F3>6000,"4th",F3>4000,"3rd",F3>2000,"2nd",F3<=2000,"1st"), OR(D3={"CO","G&P"}), IFS(F3>6000,"Jrny",F3>4500,"4th",F3>3000,"3rd",F3>1500,"2nd",F3<=1500,"1st"), OR(D3={"ST","LT"}), IFS(F3>4000,"Jrny",F3>3000,"4th",F3>2000,"3rd",F3>1000,"2nd",F3<=1000,"1st"))

[#3 · Accepted solution]

@nnvjac 

If you have Microsoft365 or Office 2021:

=LET(x, IFS(D3="HDR", 2000, OR(D3={"CO","G&P"}), 1500, OR(D3={"ST","LT"}), 1000), IFS(F3>4*x, "Jrny", F3>3*x, "4th", F3>2*x, "3rd", F3>x, "2nd", F3<=x, "1st"))

[#4]

you are a lifesaver! Thank you. I am getting data for this sheet from an Access Database and every time I refresh the data it wanted to change these columns to all be the same so I figured if I just wrote one formula I wouldn't have that issue any more. I have one more column I need a similar formula for. This column calculates training hours needed based on the Branches listed above (Column D) and the resulting period of training that is now in Column G

For LT and ST I have the following formula:
=IF(G564="2nd",72,IF(G564="1st",72,IF(G564="3rd",144,IF(G564="4th",216,IF(G564="Jrny",288)))))
For CO, G&P and HDR I have:
=IF(G307="2nd",288,IF(G307="1st",144,IF(G307="3rd",432,IF(G307="4th",576,IF(G307="Jrny",576)))))
Now I need a formula like the one you gave me before. I thought I would be able to easily figure it out using what you gave me but its taking me a minute.
Thank you so much for your assistance. This is information I use almost daily and will save me a tremendous amount of work because before I was putting the info in both excel and Access just so I could print out a pretty report for the powers that be from excel. Now I only have to enter it into Access and refresh it in Excel.

[#5]

@nnvjac 

This one is less straightforward since the progression does not follow the same pattern

72 - 72 - 144 - 216 - 288

vs

288 - 144 - 432 - 576 - 576

Try this:

=LOOKUP(G3, {"2nd","1st","3rd","4th","Jrny"}, IF(OR(D3={"LT","ST"}), {72,72,144,216,288}, {288,144,432,576,576}))

[#6]

That formula worked for everything except "1st" period which should give a result of either 72 (for LT and ST) or 144 (for CO, G&P and HDR)

[#7]

I am looking at that now and I think maybe my original formula was wrong all along. Now I'm just trying to figure out how to adjust it correctly
it should go 144, 288, 432, 576
Or 72, 144, 216, 288

[#8]

I think I finally got it correct. Thank you so much for your assistance with this!!

[#9]

Finally got the formulas all fixed to do what I need. Saved the document. Re-opened it, hit refresh data and then it reverted all the formulas to the previous ones I had input. I don't understand why it does that after saving, closing and re-opening

[#10]

@nnvjac 

I'm afraid I cannot explain that.