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

 

[Excellent022]

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.

Hi Alex. I tried to use this xlookup in an almost identical workbook and it's not working. Can you help me figure out why? I can't upload a mini-sheet but here's a picture of an example row. The right-most "episode 1/2/3/etc." column with data is what I want. The data will always be a date. I tried changing the formula by replacing the search term but there's no return value. Though no error either. I'm guessing it's the fact that it's not text?

ID

First Name

Last Name

Co

Agency

Agency2

Status

Most Recent Episode

Episode1

Amount

Percent

Status

Action

Audit

Episode2

Amount

Percent

Status

Action

Audit

Episode3

Amount

Percent

Status

Action

Audit

Episode4

Amount

Percent

Status

Action

Audit

Episode5

Amount

Percent

Status

Action

Audit

Terminated

Notes

Final Action

Need formula here

 

[Alex Blakenburg]

If the date field is always going to be blank or a date then something like this should work:

=XLOOKUP(1,(LEFT($I$1:$AO$1,7)="Episode")*($I2:$AO2<>""),$I2:$AO2,"",0,-1)

If there is sometimes text in the Episode columns and you only want to return it if it is an actual date you could try:

=XLOOKUP(1,(LEFT($I$1:$AO$1,7)="Episode")*(ISNUMBER($I2:$AO2)),$I2:$AO2,"",0,-1)

 

[Excellent022]

If the date field is always going to be blank or a date then something like this should work:

=XLOOKUP(1,(LEFT($I$1:$AO$1,7)="Episode")*($I2:$AO2<>""),$I2:$AO2,"",0,-1)

If there is sometimes text in the Episode columns and you only want to return it if it is an actual date you could try:

=XLOOKUP(1,(LEFT($I$1:$AO$1,7)="Episode")*(ISNUMBER($I2:$AO2)),$I2:$AO2,"",0,-1)

Yes it'll always be blank or have a date. It's so weird, it accepts the formula but it just returns a blank cell. I double checked the formatting.

 

[Excellent022]

Yes it'll always be blank or have a date. It's so weird, it accepts the formula but it just returns a blank cell. I double checked the formatting.

I found the issue and now it works. Thanks again! If you had any interest in helping to figure out one more formula for the Status column, I'd be very grateful.

 

[Alex Blakenburg]

If it is similar then post it here otherwise you are better off starting a new thread.

 

[Excellent022]

If it is similar then post it here otherwise you are better off starting a new thread.

I think it's similar. In the Status column to the left of the one you just figured out, I need a 1, 2, 3, 4, or 5 populated depending on how far in the series of episodes someone gets. For example, if there is a date in each of the first three episode columns, I need the status column to have a 3. All five columns = 5,

 

[Alex Blakenburg]

If your headings are accurate and you have a number at the end of the episode heading, Can you use that number ?

 

[Excellent022]

If your headings are accurate and you have a number at the end of the episode heading, Can you use that number ?

They are. If it works then yeah, that seems easy.

 

[Alex Blakenburg]

See if this does what you need:

=XLOOKUP(1,(LEFT($I$1:$AO$1,7)="Episode")*($I2:$AO2<>""),--RIGHT($I$1:$AO$1,1),"",0,-1)

 

[Excellent022]

See if this does what you need:

=XLOOKUP(1,(LEFT($I$1:$AO$1,7)="Episode")*($I2:$AO2<>""),--RIGHT($I$1:$AO$1,1),"",0,-1)

It worked. I aspire to be this good, thanks. Have a good night.