# Taking the Highest Value

#### andyscott

##### Board Regular
Hi Guys,

I was wondering how you can take the highest value in a series of cells ?

i.e. I have a series of IF statements that return TEXT values in 4 cells.

The values can either be "HL1, HL2, HL3, HL4, PT" in cells A1 through A4.

What I am trying to do is come up with a way to take the HIGHEST of these values.

i.e. so if cells A1 & A3 = "HL4" and A2 = "HL3" ..... then it takes "HL4" because its the highest value.

i.e. 2 ... so if cells A2 & A3 = "HL2" and A4 = "HL3" ..... then it takes "HL3" because its the highest value etc

Little unsure about how to do this ? Any ideas ?

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### hsk

##### Well-known Member
Are there only 4 values .... then may be this can help

=MAX(RIGHT(A1,1),RIGHT(A2,1),RIGHT(A3,1),RIGHT(A4,1))

#### tusharm

##### MrExcel MVP
How does PT compare with the various HL values? And, how does HL04 compare with HL4?

If you want to rely on XL's native ranking, sort the data in descending order and use the value in the first cell.
Hi Guys,

I was wondering how you can take the highest value in a series of cells ?

i.e. I have a series of IF statements that return TEXT values in 4 cells.

The values can either be "HL1, HL2, HL3, HL4, PT" in cells A1 through A4.

What I am trying to do is come up with a way to take the HIGHEST of these values.

i.e. so if cells A1 & A3 = "HL4" and A2 = "HL3" ..... then it takes "HL4" because its the highest value.

i.e. 2 ... so if cells A2 & A3 = "HL2" and A4 = "HL3" ..... then it takes "HL3" because its the highest value etc

Little unsure about how to do this ? Any ideas ?

#### andyscott

##### Board Regular
PT Highest

Hi Guys,

Thanks for the responses .... PT is the HIGHEST level

so HL1 is the lowest and PT is the highest .....

any idea's of the formula ? i.e. the MAX doesnt work because it relies on all 4 values .....

i.e. I need it to return the highest value no matter what

i.e. if A1 = HL1, and A2 --> A4 = 0 ... then return HL1
i.e. if A1 = HL1, A2 = HL2, A3 = HL3 and A4 = 0 .... then return HL3

etc ..... ? Thanks!

#### barry houdini

##### MrExcel MVP
Here's one way....

=LOOKUP(2,1/COUNTIF(A1:A4,{"HL1","HL2","HL3","HL4","PT"}),{"HL1","HL2","HL3","HL4","PT"})

#### andyscott

##### Board Regular
Thanks!

Legend ... works a treat! thanks! :D :D

Replies
3
Views
565
Replies
2
Views
109
Replies
4
Views
290
Replies
11
Views
316
Replies
2
Views
834

1,181,647
Messages
5,931,209
Members
436,784
Latest member
amuljono

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back