PDA

View Full Version : [SOLVED] Get Max value in a multpile term cell reference

ioncila
11-05-2014, 11:07 AM
Hi
I am trying to build a formula to this:

A
B
C
D

1
a
10
a,b,c
?

2
b
20
d
?

3
c
30
d,g,h
?

4
d
40
c,e
?

5
e
50
f
?

6
f
60
i,b,d
?

7
g
70
f,a
?

8
h
80
e,h,f
?

9
i
90
c,f
?

How do I calculate or lookup for which cell term is the max value in A1:B9 range?
Ioncila

Zlerp
11-05-2014, 12:36 PM
This code will display a message box that will tell you the highest value in cell ranges A1:B9.

You can then ctrl+F and search that value to find what cell it is in.

Sub Largest()'Cells with dates also return a value, and get covered for determining largest value. Percentages will convert and return numerics.

Dim rngs As Range
Dim maximum As Double

'Set range from which to determine largest value
Set rngs = Sheet1.Range("A1:B9")

'Worksheet function MAX returns the largest value in a range
maximum = Application.WorksheetFunction.Max(rng)

'Displays largest value
MsgBox maximum

End Sub

Hope this helps.

ioncila
11-05-2014, 04:34 PM
Is there a way to make it through a forlmula?

mikerickson
11-05-2014, 07:49 PM
If column B is sorted Ascending, you could use the CSE formula

=INDEX(B:B, MAX(IF(ISNUMBER(MATCH("*"&\$A\$1:\$A\$9&"*",C1,0)),ROW(\$A\$1:\$A\$9))), 1) in D1.

ioncila
11-06-2014, 02:50 AM
Thank you very much for your help.
It works perfect, if col B is sorted ascending.
But if not? I verified and only D1 does not match correct result. The other cells are ok.

xld
11-06-2014, 03:55 AM
Try

=MAX(IF(ISNUMBER(MATCH("*"&\$A\$1:\$A\$9&"*",C1,0)),\$B\$1:\$B\$9))

ioncila
11-06-2014, 06:00 AM
That's it
Thak you very much for your help @xld and @mickerickson