Prioritize lookup-criterias and select latest value that fullfills the prioritized lookup-critiera-CodePudding

   |      A       |  
---|--------------|--
1  |    O.K.      |    
2  |    Warning   | 
3  |    Error     | 
4  |    Warning   | 
5  |    O.K.      |
6  |    Error     |
7  |    O.K.      |
8  |    O.K.      |
9  |    O.K.      |
10 |              |
11 |              |
---|--------------|---
12 |    Warning   |
13 |              |

In the Range A1:A11 I want to

check if there is a value <> O.K.
If yes, the values with Warning should be prioritized over the values with Error and displayed accordingly in cell A12.

So far I am able to make the first step and figure out if there is a value <> O.K.

=LOOKUP(2,1/((A1:A11<>"O.K.")*(A1:A11<>"")),A1:A11)

However, with this formula the result in Cell A12 is Error.
How do I have to modify it to prioritize Warning above Error?

CodePudding user response：

Assuming no Error or Warning it should print O.K.

=XLOOKUP("Warning",A1:A9,A1:A9,XLOOKUP("Error",A1:A9,A1:A9,"O.K.",0,1),0,1)

CodePudding user response：

With Excel-365 try-

=@SORT(FILTER(A1:A11,A1:A11<>"O.K."),,-1)