What I'm attempting to do is write a formula within a conditional format that
uses an if statement and then performs a max lookup within a column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great resource!

Try this:

Select the range of cells F5:F33
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND($A$5="eligible",F5=MAX(F$5:F$33))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
What I'm attempting to do is write a formula within a conditional format
that
uses an if statement and then performs a max lookup within a column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great resource!

Bill,

Thank you for your response. I inadvertently left out part of what I was
attempting to accomplish. Your formula works perfect if cell $A$5 equals
eligible, however I am trying to conditional format cells F5:F33 based upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates eligible for
a corresponding individual in column "B", then I would like to conditional
format column "F" based upon the eligible individuals from column "A"

Once again, thank you in advance for your assistance. I been reading
various posts as well as the help feature in Excel and I am not able tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a conditional format that
uses an if statement and then performs a max lookup within a column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great resource!

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of what I was
attempting to accomplish. Your formula works perfect if cell $A$5 equals
eligible, however I am trying to conditional format cells F5:F33 based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates eligible
for
a corresponding individual in column "B", then I would like to conditional
format column "F" based upon the eligible individuals from column "A"

Once again, thank you in advance for your assistance. I been reading
various posts as well as the help feature in Excel and I am not able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a conditional format
that
uses an if statement and then performs a max lookup within a column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great resource!

Bill - I thought max would return the value in column "F" with the largest
amount
in a conditional format.

Your last formula works well to identify the individuals in column "A" who
are eligible, but I also need to identify the individual with the largest
amount in column "F" based upon meeting eligible criteria in column "A".



"T. Valko" wrote:

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of what I was
attempting to accomplish. Your formula works perfect if cell $A$5 equals
eligible, however I am trying to conditional format cells F5:F33 based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates eligible
for
a corresponding individual in column "B", then I would like to conditional
format column "F" based upon the eligible individuals from column "A"

Once again, thank you in advance for your assistance. I been reading
various posts as well as the help feature in Excel and I am not able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a conditional format
that
uses an if statement and then performs a max lookup within a column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great resource!

Let's try this. Based on the data below what cell(s) should be highlighted?

E = eligible

E...Joe...10
x...Jane...15
E...Lisa...100
E...Sam...22
x...Tom...100
E...Ann...52

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill - I thought max would return the value in column "F" with the largest
amount
in a conditional format.

Your last formula works well to identify the individuals in column "A" who
are eligible, but I also need to identify the individual with the largest
amount in column "F" based upon meeting eligible criteria in column "A".



"T. Valko" wrote:

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of what I
was
attempting to accomplish. Your formula works perfect if cell $A$5
equals
eligible, however I am trying to conditional format cells F5:F33 based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates eligible
for
a corresponding individual in column "B", then I would like to
conditional
format column "F" based upon the eligible individuals from column "A"

Once again, thank you in advance for your assistance. I been reading
various posts as well as the help feature in Excel and I am not able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a conditional
format
that
uses an if statement and then performs a max lookup within a column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great
resource!

The cell in which 100 relates to Lisa and the cell that pertains to 100 for
Tom. Thank you very much for your patience with my minimal knowledge, it's
greatly appreciated!

"T. Valko" wrote:

Let's try this. Based on the data below what cell(s) should be highlighted?

E = eligible

E...Joe...10
x...Jane...15
E...Lisa...100
E...Sam...22
x...Tom...100
E...Ann...52

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill - I thought max would return the value in column "F" with the largest
amount
in a conditional format.

Your last formula works well to identify the individuals in column "A" who
are eligible, but I also need to identify the individual with the largest
amount in column "F" based upon meeting eligible criteria in column "A".



"T. Valko" wrote:

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of what I
was
attempting to accomplish. Your formula works perfect if cell $A$5
equals
eligible, however I am trying to conditional format cells F5:F33 based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates eligible
for
a corresponding individual in column "B", then I would like to
conditional
format column "F" based upon the eligible individuals from column "A"

Once again, thank you in advance for your assistance. I been reading
various posts as well as the help feature in Excel and I am not able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a conditional
format
that
uses an if statement and then performs a max lookup within a column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great
resource!

Ok, I'm officially confused!

The cell in which 100 relates to Lisa and the
cell that pertains to 100 for Tom.

Why would "Tom 100" be highlighted? Tom is not "eligible".

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
The cell in which 100 relates to Lisa and the cell that pertains to 100
for
Tom. Thank you very much for your patience with my minimal knowledge,
it's
greatly appreciated!

"T. Valko" wrote:

Let's try this. Based on the data below what cell(s) should be
highlighted?

E = eligible

E...Joe...10
x...Jane...15
E...Lisa...100
E...Sam...22
x...Tom...100
E...Ann...52

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill - I thought max would return the value in column "F" with the
largest
amount
in a conditional format.

Your last formula works well to identify the individuals in column "A"
who
are eligible, but I also need to identify the individual with the
largest
amount in column "F" based upon meeting eligible criteria in column
"A".



"T. Valko" wrote:

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of what
I
was
attempting to accomplish. Your formula works perfect if cell $A$5
equals
eligible, however I am trying to conditional format cells F5:F33
based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates
eligible
for
a corresponding individual in column "B", then I would like to
conditional
format column "F" based upon the eligible individuals from column
"A"

Once again, thank you in advance for your assistance. I been
reading
various posts as well as the help feature in Excel and I am not able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a conditional
format
that
uses an if statement and then performs a max lookup within a
column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great
resource!

I'm sorry, I only looked at the cell value of 100 for Tom not whether he was
eligible. You are correct, the only cell that I would want to be
conditionally formatted would be the 100 that relates to Lisa.

"T. Valko" wrote:

Ok, I'm officially confused!

The cell in which 100 relates to Lisa and the
cell that pertains to 100 for Tom.

Why would "Tom 100" be highlighted? Tom is not "eligible".

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
The cell in which 100 relates to Lisa and the cell that pertains to 100
for
Tom. Thank you very much for your patience with my minimal knowledge,
it's
greatly appreciated!

"T. Valko" wrote:

Let's try this. Based on the data below what cell(s) should be
highlighted?

E = eligible

E...Joe...10
x...Jane...15
E...Lisa...100
E...Sam...22
x...Tom...100
E...Ann...52

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill - I thought max would return the value in column "F" with the
largest
amount
in a conditional format.

Your last formula works well to identify the individuals in column "A"
who
are eligible, but I also need to identify the individual with the
largest
amount in column "F" based upon meeting eligible criteria in column
"A".



"T. Valko" wrote:

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of what
I
was
attempting to accomplish. Your formula works perfect if cell $A$5
equals
eligible, however I am trying to conditional format cells F5:F33
based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates
eligible
for
a corresponding individual in column "B", then I would like to
conditional
format column "F" based upon the eligible individuals from column
"A"

Once again, thank you in advance for your assistance. I been
reading
various posts as well as the help feature in Excel and I am not able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a conditional
format
that
uses an if statement and then performs a max lookup within a
column.

For example: =if a5="eligible", =max(f5:f33) and then highlight the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great
resource!

Try this:

=AND(A5="eligible",F5=MAX(IF(A$5:A$33="eligible",F $5:F$33)))

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
I'm sorry, I only looked at the cell value of 100 for Tom not whether he
was
eligible. You are correct, the only cell that I would want to be
conditionally formatted would be the 100 that relates to Lisa.

"T. Valko" wrote:

Ok, I'm officially confused!

The cell in which 100 relates to Lisa and the
cell that pertains to 100 for Tom.

Why would "Tom 100" be highlighted? Tom is not "eligible".

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
The cell in which 100 relates to Lisa and the cell that pertains to 100
for
Tom. Thank you very much for your patience with my minimal knowledge,
it's
greatly appreciated!

"T. Valko" wrote:

Let's try this. Based on the data below what cell(s) should be
highlighted?

E = eligible

E...Joe...10
x...Jane...15
E...Lisa...100
E...Sam...22
x...Tom...100
E...Ann...52

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill - I thought max would return the value in column "F" with the
largest
amount
in a conditional format.

Your last formula works well to identify the individuals in column
"A"
who
are eligible, but I also need to identify the individual with the
largest
amount in column "F" based upon meeting eligible criteria in column
"A".



"T. Valko" wrote:

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of
what
I
was
attempting to accomplish. Your formula works perfect if cell
$A$5
equals
eligible, however I am trying to conditional format cells F5:F33
based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates
eligible
for
a corresponding individual in column "B", then I would like to
conditional
format column "F" based upon the eligible individuals from column
"A"

Once again, thank you in advance for your assistance. I been
reading
various posts as well as the help feature in Excel and I am not
able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a
conditional
format
that
uses an if statement and then performs a max lookup within a
column.

For example: =if a5="eligible", =max(f5:f33) and then highlight
the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great
resource!

Bill - That worked perfect! Thank you for all your help and patience!

"T. Valko" wrote:

Try this:

=AND(A5="eligible",F5=MAX(IF(A$5:A$33="eligible",F $5:F$33)))

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
I'm sorry, I only looked at the cell value of 100 for Tom not whether he
was
eligible. You are correct, the only cell that I would want to be
conditionally formatted would be the 100 that relates to Lisa.

"T. Valko" wrote:

Ok, I'm officially confused!

The cell in which 100 relates to Lisa and the
cell that pertains to 100 for Tom.

Why would "Tom 100" be highlighted? Tom is not "eligible".

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
The cell in which 100 relates to Lisa and the cell that pertains to 100
for
Tom. Thank you very much for your patience with my minimal knowledge,
it's
greatly appreciated!

"T. Valko" wrote:

Let's try this. Based on the data below what cell(s) should be
highlighted?

E = eligible

E...Joe...10
x...Jane...15
E...Lisa...100
E...Sam...22
x...Tom...100
E...Ann...52

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill - I thought max would return the value in column "F" with the
largest
amount
in a conditional format.

Your last formula works well to identify the individuals in column
"A"
who
are eligible, but I also need to identify the individual with the
largest
amount in column "F" based upon meeting eligible criteria in column
"A".



"T. Valko" wrote:

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of
what
I
was
attempting to accomplish. Your formula works perfect if cell
$A$5
equals
eligible, however I am trying to conditional format cells F5:F33
based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates
eligible
for
a corresponding individual in column "B", then I would like to
conditional
format column "F" based upon the eligible individuals from column
"A"

Once again, thank you in advance for your assistance. I been
reading
various posts as well as the help feature in Excel and I am not
able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a
conditional
format
that
uses an if statement and then performs a max lookup within a
column.

For example: =if a5="eligible", =max(f5:f33) and then highlight
the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great
resource!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill - That worked perfect! Thank you for all your help and patience!

"T. Valko" wrote:

Try this:

=AND(A5="eligible",F5=MAX(IF(A$5:A$33="eligible",F $5:F$33)))

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
I'm sorry, I only looked at the cell value of 100 for Tom not whether
he
was
eligible. You are correct, the only cell that I would want to be
conditionally formatted would be the 100 that relates to Lisa.

"T. Valko" wrote:

Ok, I'm officially confused!

The cell in which 100 relates to Lisa and the
cell that pertains to 100 for Tom.

Why would "Tom 100" be highlighted? Tom is not "eligible".

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
The cell in which 100 relates to Lisa and the cell that pertains to
100
for
Tom. Thank you very much for your patience with my minimal
knowledge,
it's
greatly appreciated!

"T. Valko" wrote:

Let's try this. Based on the data below what cell(s) should be
highlighted?

E = eligible

E...Joe...10
x...Jane...15
E...Lisa...100
E...Sam...22
x...Tom...100
E...Ann...52

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill - I thought max would return the value in column "F" with
the
largest
amount
in a conditional format.

Your last formula works well to identify the individuals in
column
"A"
who
are eligible, but I also need to identify the individual with the
largest
amount in column "F" based upon meeting eligible criteria in
column
"A".



"T. Valko" wrote:

Ok, then what does the MAX have to do with it?

For example: =if a5="eligible", =max(f5:f33) and then
highlight the applicable max cell in column f .

See if this does what you want:

Formula Is:
=A5="eligible"

--
Biff
Microsoft Excel MVP


"CARFL97" wrote in message
...
Bill,

Thank you for your response. I inadvertently left out part of
what
I
was
attempting to accomplish. Your formula works perfect if cell
$A$5
equals
eligible, however I am trying to conditional format cells
F5:F33
based
upon
what the corresponding cells in column "A" return.

In my spreadsheet, (see example below) if column "A" indicates
eligible
for
a corresponding individual in column "B", then I would like to
conditional
format column "F" based upon the eligible individuals from
column
"A"

Once again, thank you in advance for your assistance. I been
reading
various posts as well as the help feature in Excel and I am
not
able
tofind
an answer. I'm a newbie to formula writing in Excel.



Row Column "A" Column"B"
5 DNQ Bix
6 Eligible Brandon
7 DNQ Cheeta
8 Eligible Chris
9 Eligible Gary
10 Eligible Glenn
11 DNQ Greg N
12 DNQ JP
13 Eligible Keith
14 Eligible Mike
15 DNQ Rich
16 DNQ Riz
17 Eligible Ryan
18 Eligible Scott B
19 Eligible Scott S
20 Eligible Seth
21 DNQ Todd


"CARFL97" wrote:

What I'm attempting to do is write a formula within a
conditional
format
that
uses an if statement and then performs a max lookup within a
column.

For example: =if a5="eligible", =max(f5:f33) and then
highlight
the
applicable max cell in column f .

Any help would be greatly appreciated. This forum is a great
resource!