IFS (?) Formula help

[blaix]

Hey crowd,

I have one IFS function that currently works, however I need it to make another consideration and am coming up blank with how to work around.

My current working function is
=IF(M4>0,M4,(INDEX(INSTRUCTIONS!$K$3:$K$4,MATCH(B4,INSTRUCTIONS!$J$3:$J$4,0))))

Essentially I want Column O (Estimated Invoice) to display an amount (found in four cells in INSTRUCTIONS sheet) based off the entity is a business or individual as we charge differing invoice rates; I have it look in Column M (Unpaid Invoices) and if there is a value there, I want it to return that value, if there is no value, I want it to return the value from INSTRTUCTIONS based off Business/Individual. This is all working fine within the above formula.

HOWEVER I need it to now consider if there is an already paid invoice in Column N (Prior Paid Invoice), I need Column O to return 0.

Please let me know if you need to upload a mini sheet for this, but ideally I just need to add to this already functioning formula (somehow) if N4>0 then return 0. If N4<0 then return the value within INSTRUCTIONS.

 

[etaf]

you could do a nested IF or use IFS()
what comes in order
M4 , then N4 , then lookup

=IF(M4>0,M4, If( N4>0, 0, (INDEX(INSTRUCTIONS!$K$3:$K$4,MATCH(B4,INSTRUCTIONS!$J$3:$J$4,0)))))

 

[Fluff]

How about

=if(n4>0,0,IF(M4>0,M4,(INDEX(INSTRUCTIONS!$K$3:$K$4,MATCH(B4,INSTRUCTIONS!$J$3:$J$4,0)))))

 

[blaix]

Holy smokes you guys are quick! The nested function was what I was looking for, thank you!

Sidenote - I am self taught/self Googled and really appreciate communities like this. It especially helps getting the answer then reverse engineering it to learn it so I can understand better moving forward/manipulating myself. Thank you for existing.

 

[etaf]

you are welcome , as Fluff has shown the N4 tested first , if you need that , then use Fluff formula
basically the nested IF works Left to Right