Return cell address based on rank of value

[ChristineJ]

My worksheet has values in cells G5, G6, G7, and G8. These values will constantly change. No cell value will ever be the same as another cell value.

I'd like the cell address with the highest value to appear in cell J1, the cell address with the second highest value to appear in cell K1, the cell address with the second lowest value to appear in cell L1, and the cell address with the lowest value to appear in cell M1. Each cell address should appear within a SUM function.

Example: G5=10, G6=4, G7=11, G8 = 6 so J1=SUM(G7), K1=SUM(G5), L1=SUM(G8), M1=SUM(G6)

Can this be done? Thanks

 

[arthurbr]

In J1 and drag right

=SUM(INDEX($G$5:$J$5,MATCH(LARGE($G$5:$J$5,COLUMN()-9),$G$5:$J$5,0)))

The INDEX function, when embedded, returns a reference to a cell so you don't really need the address

 

[ChristineJ]

Thanks for the reply! I got a NUM error message when I dragged to the right.

However, you post gave me another idea and I was able to resolve it differently. Appreciate it.

 

[arthurbr]

It worked perfectly for me. More explanation on the NUM error

 

[Peter_SSs]

It worked perfectly for me.

.. though you are working with a different layout to the one posted by the OP.

values in cells G5, G6, G7, and G8

=SUM(INDEX($G$5:$J$5,MATCH(LARGE($G$5:$J$5,COLUMN()-9),$G$5:$J$5,0)))

Example: G5=10, G6=4, G7=11, G8 = 6 so J1=SUM(G7), K1=SUM(G5), L1=SUM(G8), M1=SUM(G6)

I was able to resolve it differently

I'm not sure what you eventually came up with but what about like this?
(As mentioned by @arthurbr, I'm also not sure why you wanted the cell address specifically since the sum of one numerical cell will always be that cell's value)

24 02 20.xlsm
	G	H	I	J	K	L	M
1				11	10	6	4
2
3
4
5	10
6	4
7	11
8	6
Order

Cell Formulas
Range	Formula
J1:M1	J1	=TRANSPOSE(SORT(G5:G8,,-1))
Dynamic array formulas.

 

[ChristineJ]

Thanks, Peter_SSs! The SORT function is exactly what I need.

I used IF/SMALL/LARGE functions to get the result, which is much clunkier.

I know this seems like an odd request. I'm providing feedback to students in strings that contain cell references, and I need those cell references to be relative and change if rows or columns are inserted or deleted. I just use the SUM function so they are relative. Once the rows/columns are inserted/deleted, I strip out the "=SUM(" and ")" and am left with just the cell address in another cell. No doubt there is a more efficient way to do this process as well, but I am already invested in doing it this way in my project.

Again, thanks for your help!

 

[Peter_SSs]

That makes no sense at all to me I'm afraid, but if you really want the cell addresses then try ..

24 02 20.xlsm
	G	H	I	J	K	L	M
1				G7	G5	G8	G6
2
3
4
5	10
6	4
7	11
8	6
Order

Cell Formulas
Range	Formula
J1:M1	J1	=ADDRESS(MATCH(LARGE($G$5:$G$8,COLUMNS($J:J)),$G:$G,0),COLUMN($G$5),4)

 

[ChristineJ]

Awesome! Thank you.

 

[Peter_SSs]

You're welcome.