Search range and give me text with highest rating

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JustinP - 05 Dec 2006 07:36 GMT

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?

Reply to this Message

JustinP - 05 Dec 2006 10:46 GMT

Please help with this someone! it's time sensitive!

> I have a bunch of columns with one of five strings on each row:
>
[quoted text clipped - 19 lines]
>
> Any ideas?

Reply to this Message

Lori - 05 Dec 2006 12:21 GMT

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

> Please help with this someone! it's time sensitive!
>
[quoted text clipped - 21 lines]
> >
> > Any ideas?

Reply to this Message

Lori - 05 Dec 2006 12:23 GMT

Replace 6 with 99 in above formula

> =INDEX(A3:J3,LOOKUP(6,MATCH({"Not Yet Assessed", "Not Required",
> "Required", "Lodged", "Approved"},A3:J3,0)))
[quoted text clipped - 24 lines]
> > >
> > > Any ideas?

Reply to this Message

JustinP - 05 Dec 2006 12:33 GMT

Thank you very much Lori, greatly appreciated.

> Replace 6 with 99 in above formula
>
[quoted text clipped - 26 lines]
> > > >
> > > > Any ideas?

Reply to this Message

Lori - 05 Dec 2006 13:54 GMT

Thanks for the comeback...glad it worked.

> Thank you very much Lori, greatly appreciated.
>
[quoted text clipped - 28 lines]
> > > > >
> > > > > Any ideas?

Reply to this Message