what formula?

meta182 · Mar 27th, 2002, 06:47 PM

I want to find highest number in, let's say J2:J25 and whatever cell has that number I want to put the persons name that's in A to a new cell and add two different cell so I get the result that looks like:

Mike wins $15.00

Can it be done with excel?

Bob Umlas · Mar 27th, 2002, 07:07 PM

Sure -- =INDEX(A2:A25,MATCH(MAX(J2:J25),J2:J25,0))&" wins "&text(MAX(J2:J25),"$#,##0.00")

Bob Umlas
Excel MVP

Yogi Anand · Mar 27th, 2002, 07:17 PM

Quote:

On 2002-03-27 17:47, meta182 wrote:
I want to find highest number in, let's say J2:J25 and whatever cell has that number I want to put the persons name that's in A to a new cell and add two different cell so I get the result that looks like:

Mike wins $15.00

Can it be done with excel?

Hi meta182:
I took your sample data and placed in columns I and J as under
columnI ColumnJ
Mike 2.00
Tiger 13.00
Mark 9.00
Mike 15.00
Alan 11.00

then the following formula gives the intended result:

=INDEX(I2:I6,MATCH(MAX(J2:J6),J2:J6,0),1)&" Wins $"&TEXT(MAX(J2:J6),"00.00")

Mike Wins $15.00

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

Yogi Anand · Mar 27th, 2002, 07:21 PM

Hi Bob Ulmas:
I may have posted while yours was on the way ... Great, neat and compact!

meta182 · Mar 27th, 2002, 07:26 PM

this is my first experience with excel.

Your answers are great Thank you Thank you Thank you

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 06:47 PM	#1
meta182 New Member Join Date: Mar 2002 Location: mike Posts: 4	I want to find highest number in, let's say J2:J25 and whatever cell has that number I want to put the persons name that's in A to a new cell and add two different cell so I get the result that looks like: Mike wins $15.00 Can it be done with excel?

Mar 27th, 2002, 07:07 PM	#2
Bob Umlas Board Regular Join Date: Mar 2002 Location: New York Posts: 71	Sure -- =INDEX(A2:A25,MATCH(MAX(J2:J25),J2:J25,0))&" wins "&text(MAX(J2:J25),"$#,##0.00") Bob Umlas Excel MVP

Mar 27th, 2002, 07:21 PM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Bob Ulmas: I may have posted while yours was on the way ... Great, neat and compact!

Mar 27th, 2002, 07:26 PM	#5
meta182 New Member Join Date: Mar 2002 Location: mike Posts: 4	this is my first experience with excel. Your answers are great Thank you Thank you Thank you