IFS with OR Formula not working

[Excellent022]

I have a deliverable tracker that I need to update in include "unpaid" as a final status. My spreadsheet worked with the following formula:
=IFS([@[Deliverable Accepted 1?]]="","Pending",[@[Deliverable Accepted 1?]]="N/A","N/A",OR([@[Deliverable Accepted 1?]]="Yes",[@[Deliverable Accepted 2?]]="Yes",[@[Deliverable Accepted 3?]]="Yes"),"Yes",OR([@[Deliverable Accepted 1?]]="Monitor",[@[Deliverable Accepted 2?]]="Monitor",[@[Deliverable Accepted 3?]]="Monitor"),"Monitor",[@[Deliverable Accepted 1?]]="No","No")

However, when I added "unpaid" as a data validation list option to the last Deliverable status column (Deliverable Accepted 1?) and updated to formula to the following, it keeps the result cell "no".

=IFS([@[Deliverable Accepted 1?]]="","Pending",[@[Deliverable Accepted 1?]]="N/A","N/A",OR([@[Deliverable Accepted 1?]]="Yes",[@[Deliverable Accepted 2?]]="Yes",[@[Deliverable Accepted 3?]]="Yes"),"Yes",OR([@[Deliverable Accepted 1?]]="Monitor",[@[Deliverable Accepted 2?]]="Monitor",[@[Deliverable Accepted 3?]]="Monitor"),"Monitor",OR([@[Deliverable Accepted 1?]]="No",[@[Deliverable Accepted 2?]]="No",[@[Deliverable Accepted 3?]]="No"),"No",[@[Deliverable Accepted 3?]]="Unpaid","Unpaid")

Here's what my new formula returns:

Final Status	Deliverable Accepted 1?	Deliverable Accepted 2?	Deliverable Accepted 3?
Yes	No	No	Yes
No	No	No	No
Monitor	No		Monitor
No	No	No	Unpaid
Monitor	Monitor	Monitor	Unpaid

 

[SunnyAlv]

Ur formula is correct, it's just that the priority of "UNPAID" must be the earliest if you want that kind of result, like this

=IFS([@[Deliverable Accepted 3?]]="Unpaid","Unpaid",[@[Deliverable Accepted 1?]]="","Pending",[@[Deliverable Accepted 1?]]="N/A","N/A",OR([@[Deliverable Accepted 1?]]="Yes",[@[Deliverable Accepted 2?]]="Yes",[@[Deliverable Accepted 3?]]="Yes"),"Yes",OR([@[Deliverable Accepted 1?]]="Monitor",[@[Deliverable Accepted 2?]]="Monitor",[@[Deliverable Accepted 3?]]="Monitor"),"Monitor",OR([@[Deliverable Accepted 1?]]="No",[@[Deliverable Accepted 2?]]="No",[@[Deliverable Accepted 3?]]="No"),"No")

 

[Alex Blakenburg]

Are you sure its not just a matter of taking the last status running from left to right ?

Book1
	A	B	C	D
1	Final Status	Deliverable Accepted 1?	Deliverable Accepted 2?	Deliverable Accepted 3?
2	Yes	No	No	Yes
3	No	No	No	No
4	Monitor	No		Monitor
5	Unpaid	No	No	Unpaid
6	Unpaid	Monitor	Monitor	Unpaid
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=XLOOKUP(1,--($B2:$D2<>""),$B2:$D2,"N/A",0,-1)

 

[Excellent022]

Are you sure its not just a matter of taking the last status running from left to right ?

Book1
	A	B	C	D
1	Final Status	Deliverable Accepted 1?	Deliverable Accepted 2?	Deliverable Accepted 3?
2	Yes	No	No	Yes
3	No	No	No	No
4	Monitor	No		Monitor
5	Unpaid	No	No	Unpaid
6	Unpaid	Monitor	Monitor	Unpaid
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=XLOOKUP(1,--($B2:$D2<>""),$B2:$D2,"N/A",0,-1)

This is a much simpler formula; however, in my spreadsheet I have review dates in between the "deliverable acceptable" columns--three columns in between each. Is there a way to exclude those columns from the range?

 

[Alex Blakenburg]

Show us all the actual column headings. Is there a pattern to their naming ?

 

[Excellent022]

Show us all the actual column headings. Is there a pattern to their naming ?

Final Status	Accepted 1?	Returned Date:	Date Received 2:	Reviewed Date 2:	Accepted 2?	Returned Date 2:	Date Received 3:	Reviewed Date 3:	Accepted 3?

 

[Alex Blakenburg]

See if this works for you:
(It assumes that you want the columns that start with the word "Accepted")

20240705 2D Lookup Excellent022.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Final Status	Accepted 1?	Returned Date:	Date Received 2:	Reviewed Date 2:	Accepted 2?	Returned Date 2:	Date Received 3:	Reviewed Date 3:	Accepted 3?
2	Yes	No				No				Yes
3	No	No				No				No
4	Monitor	No								Monitor
5	Unpaid	No				No				Unpaid
6	Unpaid	Monitor				Monitor				Unpaid
Later v02

Cell Formulas
Range	Formula
A2:A6	A2	=XLOOKUP(1,(LEFT($B$1:$J$1,8)="Accepted")*($B2:$J2<>""),$B2:$J2,"",0,-1)

 

[Phuoc]

Or try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Final Status	Accepted 1?	Returned Date:	Date Received 2:	Reviewed Date 2:	Accepted 2?	Returned Date 2:	Date Received 3:	Reviewed Date 3:	Accepted 3?
2	Yes	No				No				Yes
3	No	No				No				No
4	Monitor	No								Monitor
5	No	No				No				Unpaid
6	Monitor	Monitor				Monitor				Unpaid
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=XLOOKUP(1,(LEFT($B$1:$J$1,8)="Accepted")*($B2:$J2<>"")*($B2:$J2<>"Unpaid"),$B2:$J2,"",0,-1)

 

[Excellent022]

See if this works for you:
(It assumes that you want the columns that start with the word "Accepted")

20240705 2D Lookup Excellent022.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Final Status	Accepted 1?	Returned Date:	Date Received 2:	Reviewed Date 2:	Accepted 2?	Returned Date 2:	Date Received 3:	Reviewed Date 3:	Accepted 3?
2	Yes	No				No				Yes
3	No	No				No				No
4	Monitor	No								Monitor
5	Unpaid	No				No				Unpaid
6	Unpaid	Monitor				Monitor				Unpaid
Later v02

Cell Formulas
Range	Formula
A2:A6	A2	=XLOOKUP(1,(LEFT($B$1:$J$1,8)="Accepted")*($B2:$J2<>""),$B2:$J2,"",0,-1)

Yep, that worked! Thanks for simplifying it for me. Love that I can add other statuses if needed.

 

[Alex Blakenburg]

Glad we could help. It's always good to show us a sample that has the characteristics of your real data. We are generally looking for patterns and data types.