Look up highest value

[motilulla]

Hello,

A............B..................C..................D..................E..................F
1--------------------------------------------------------------------------
2--------------------------------------------------------------------------
3---------22 / 183---215 / 324-----59 / 386---------------------------
4--------------------------------------------------------------------------
5--------------------------------------------------------------------------
6--------------------------------------------------------------------------
7----------W|T|A-----W|T|B---------W|T|C----------------------------
8-------------------------------------------------------------------------
9------------------------------------------------------------------------B


I am looking some Formula or VBA which can look in the row 3, column B, C & D Left values before "/" which are 22, 215, & 59 look the highest among 3 values and find in the same column in row 7 most right word and place in the cell F9

For example C3 has highest value 215 so far looking in same column C in the row C7 is the most right word is "B" place in the cell F9.

Thank you all
Excel 2000
Regards,
Moti

 

[gmhumphr]

In cell F9, enter the formula:

=RIGHT(INDEX(B7:D7,0,MATCH(MAX(VALUE(LEFT(B3:D3,SEARCH(" /",B3:D3)-1))),VALUE(LEFT(B3:D3,SEARCH(" /",B3:D3)-1)),0)),1)

and press CTRL + SHIFT + ENTER to commit it as an array formula. This will return the result B.

 

[Scott T]

If there is a tie for highest value this will return the first match. that is 77/183 60/136 77/183 will return the value from column B since that is the first match found.

Excel 2010
	A	B	C	D	E	F
1
2
3		22/183	215/324	59/286
4
5
6
7		W|T|A	W|T|B	W|T|C
8
9						B
Sheet7

Cell Formulas
Range	Formula
F9	{=TRIM(RIGHT(SUBSTITUTE(INDEX(B7:D7,MATCH(MAX(TRIM(LEFT(B3:D3,SEARCH("/",B3:D3)-1))+0),TRIM(LEFT(B3:D3,SEARCH("/",B3:D3)-1))+0,0)),"|",REPT(" ",200)),200))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gmhumphr]

What it does (follow along with Evaluate Formula to see it in action, words in CAPS are the formulas used in plain text description):

Using an array formula, it parses the characters to the LEFT of the result of a SEARCH of a space and a slash [" /"] - {"22,"215","59"}
Converts those to a numeric VALUE (stored as text/string), then looks for the MAX - 215.
Knowing the largest, MATCH the largest to its position within the array - 2nd position.
INDEX the row you want info from and pull the value from the 2nd position - "W|T|B".
Parse the single RIGHTmost character from the string - "B".

 

[motilulla]

@Scott T and @gmhumphr,

Thank you for both the replies. Both formulas are giving the requested results.

Kind Regards,
Moti

 

[motilulla]

In cell F9, enter the formula:

=RIGHT(INDEX(B7:D7,0,MATCH(MAX(VALUE(LEFT(B3:D3,SEARCH(" /",B3:D3)-1))),VALUE(LEFT(B3:D3,SEARCH(" /",B3:D3)-1)),0)),1)

and press CTRL + SHIFT + ENTER to commit it as an array formula. This will return the result B.

Hello gmhumphr,
how are you doing?

In the row 7, in the third alphabet Instead of a letter if change it to a number from 1 to 9 I get the perfect result but when I convert it into value I find one space before that number. How can I eliminate this space?

Please, can you modify the formula to not get this space.

Thank you for your time.

Kind Regards,
Moti

 

[motilulla]

Any suggestion please ?

Thank you

 

[gmhumphr]

Hello gmhumphr,
how are you doing?

In the row 7, in the third alphabet Instead of a letter if change it to a number from 1 to 9 I get the perfect result but when I convert it into value I find one space before that number. How can I eliminate this space?

Please, can you modify the formula to not get this space.

Thank you for your time.

Kind Regards,
Moti

Moti - Please clarify the issue and give me the exact contents of the cell in row 7 that is matched so I can understand. What does the formula give you and what do you want it to give you instead.
Currently the RIGHT formula takes the single, last character of the matched cell. Unless there was a trailing space, I would expect that the formula returns an apparently blank cell (really, a space character) and not a number with a space in front of it.

 

[motilulla]

Moti - Please clarify the issue and give me the exact contents of the cell in row 7 that is matched so I can understand. What does the formula give you and what do you want it to give you instead.
Currently the RIGHT formula takes the single, last character of the matched cell. Unless there was a trailing space, I would expect that the formula returns an apparently blank cell (really, a space character) and not a number with a space in front of it.

Hello gmhumphr,

It’s a strange situation now it is working perfect there is no space in before numbers.

Sorry for the inconvenience.

Thank you for the response

Kind Regards,
Moti