Search range and give me text with highest rating

J

JustinP

I have a bunch of columns with one of five strings on each row:

Approved
Lodged
Required
Not Required
Not Yet Assessed

I need to search through a range (i.e. A3:J3) and pull out the string
with the highest rating (Not Yet Assessed = 1 --> Approved = 5). The
ratings relative and always constant (i.e. Lodged is alway above
Required and Required is always about Not Required)

Example

A3 B3 C3 D3

Lodged Required Not Required Not Required

I need a formula in E3 that recognises that Required exists in the
range and takes precedence above the other strings so E5 = Lodged

Any ideas?
 
L

Lori

=INDEX(A3:J3,LOOKUP(6,MATCH({"Not Yet Assessed", "Not Required",
"Required", "Lodged", "Approved"},A3:J3,0)))
 
Top