Get Max value in a multpile term cell reference

[ioncila]

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?
Many Thanks in advance
Ioncila

[Zlerp]

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]

Is there a way to make it through a forlmula?

[mikerickson]

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]

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.

[Bob Phillips]

Try

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

[ioncila]

That's it
Thak you very much for your help @xld and @mickerickson