Showing the contents of a cell based on another cell

  • Thread starter Thread starter Daminc
  • Start date Start date
D

Daminc

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?
 
=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
 
This: =INDIRECT("A"&(MATCH(B10,B2:B5,1)+1)) worked great

I'll have to look at the INDIRECT function more closely I think :)
 
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.
-------------------



I spoke too soon I think. That formula works for all of them apart fro
"Class 1" which returns "Class 4" instead :confused
 
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
 
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.
 
Back
Top