Xlookup Formula Issue

[Sundance_Kid]

Hi,
I am trying to use a formula to return the year based on a date, and if the date is not within the range in the lookup table I want it to return the word "Problem". However, I have two dates out of five which should not fall between the dates in the table, yet only one is returning "Problem".

So, I clearly have entered something wrong. Please see below.

Any ideas?

 

[hagia_sofia]

Hello, Excel is working properly here because you picked up -1 as the match mode, i.e. exact match or next smallest which is 2024. So, how about using:

=XLOOKUP(YEAR(J5:J9),F7:F11,F7:F11,"problem")

 

[Sundance_Kid]

Hello, Excel is working properly here because you picked up -1 as the match mode, i.e. exact match or next smallest which is 2024. So, how about using:

=XLOOKUP(YEAR(J5:J9),F7:F11,F7:F11,"problem")

Thanks, yep I didn't think excel was doing anything wrong but more my formula was not accurate.

Ok, so -1 pulling next smallest makes sense then.

Not sure your formula is working either, I am trying to return the value to cells J5 to J9.

Also, where it is taking the value from the cells F7 to F11, it may not always be a year, it may say Quarter 4 or something else, so not sure is the Year part of the formula required?

 

[hagia_sofia]

My apologies, it should be I5:I9 instead of J. As for the latter question, what possible values are there - could you post some examples?

 

[Sundance_Kid]

My apologies, it should be I5:I9 instead of J. As for the latter question, what possible values are there - could you post some examples?

Hi, your formula still doesnt seem to work.

As an aside in the column where it says year, if I decide to put something like a text instead of the year, I would want that returned.

 

[hagia_sofia]

Alright, how about this then:

=FILTER($F$7:$F$11,($D$7:$D$11<=I5)*($E$7:$E$11>=I5),"problem")