I sure it's quite simple but I'm having all sorts of problems with
this:

Background:

one cell gives me the MAX value of a range of cells
I want to then use that value to refer back to the range of cells and
return the value one column to the left

This formula returns "A4" which is the cell number for this result I
want to show but it just enters A4 not the contents of cell A4

="A"&(MATCH(B10,B2:B5,1)+1)

does that make sense?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=483454

=INDIRECT("A"&(MATCH(B10,B2:B5,1)+1))

--
Kind regards,

Niek Otten

"Daminc" wrote in
message ...

I sure it's quite simple but I'm having all sorts of problems with
this:

Background:

one cell gives me the MAX value of a range of cells
I want to then use that value to refer back to the range of cells and
return the value one column to the left

This formula returns "A4" which is the cell number for this result I
want to show but it just enters A4 not the contents of cell A4

="A"&(MATCH(B10,B2:B5,1)+1)

does that make sense?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=483454

=INDEX(A2:A5,MATCH(B10,B2:B5,0)) or

=INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0)) does it in one formula

HTH

Hi Daminc

Try
=INDIRECT("A"&(MATCH(B10,B2:B5,1)+1))
or
=INDEX(A:A,MATCH(B10,B2:B5,1)+1)

Regards

Roger Govier


Daminc wrote:
I sure it's quite simple but I'm having all sorts of problems with
this:

Background:

one cell gives me the MAX value of a range of cells
I want to then use that value to refer back to the range of cells and
return the value one column to the left

This formula returns "A4" which is the cell number for this result I
want to show but it just enters A4 not the contents of cell A4

="A"&(MATCH(B10,B2:B5,1)+1)

does that make sense?

This: =INDIRECT("A"&(MATCH(B10,B2:B5,1)+1)) worked great

I'll have to look at the INDIRECT function more closely I think :)


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=483454

Code:
--------------------
Term 1 Term 2 Term 3 Term 4
Class 1 200.0 98.0
Class 2 111.0 99.0
Class 3 100.0 97.9
Class 4 99.0 97.2

Average 127.5 98.0 No numbers No numbers

Winner Class 4 Class 2 #N/A #N/A
200.0 99.0 0.0 0.0
--------------------



I spoke too soon I think. That formula works for all of them apart from
"Class 1" which returns "Class 4" instead


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=483454

I suspect the problem may be due to your using 1 as the third parameter
in MATCH - this requires the list to be in ascending sequence. I
suggest using 0 as the third parameter and/or using the formulas I
provided above, since your lists are not in sequence and you require an
exact match.

HTH

Changing the third parameter to 0 did the job. Thanks DOR


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=483454

You may have a problem with the +1 also following the match if youuse 0
as the third parameter, but a bigger question is what do you do if you
have two or more classes in a tie for first place?

DOR

The +1 doesn't seem to be a problem at the moment and as for a tied
first place...well I'm not sure at the moment. I'm going to have to
face that hurdle if and when it arises.

Cheers for your help. If you have any other suggestions I'd be glad to
learn from them.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=483454