How to always return the numbers of the first cell of a column Based on their value?

[Goldenrules]

Good day great minds, I have been battling with this fommula for while now and I need yours help on it.

In the first image is a list of 1 - 49 numbers with the trands of each numbers.

And on the second image is the lists of the highest 10 numbers trends and lowest 20 numbers trends.

I used this formula "=LARGE($CM4:$EI4,1) for the largest 10 number which worked perfectly will, including the lowest 20 too.

But instead of it given me those values for me, I want it to return the numbers.

I was able to come up with this formula "=INDEX($BZ$1:$DV$1,1,MATCH(MAX($BZ5:$DV5),$BZ5:$DV5,0))" but it's not working well.

Please kindly help.

 

[James006]

Hi,
Have you tested : =INDEX($BZ$1:$DV$5,1,MATCH(MAX($BZ5:$DV5),$BZ5:$DV5,0))

 

[Goldenrules]

Hi,
Have you tested : ,MATCH(MAX($BZ5:$DV5),$BZ5:$DV5,0))

It's can't be =INDEX($BZ$1:$DV$5,1 because it's should only look across which rows

 

[Goldenrules]

Please permit me to rephrase the question;
How to get or return column header based on specific row value in Excel?

In the first image is a list of 1 - 49 numbers with the trands of each numbers.

And on the second image is the lists of the highest 10 numbers trends and lowest 20 numbers trends.

I used this formula "=LARGE($CM4:$EI4,1) for the largest 10 number which worked perfectly will, including the lowest 20 too.

But instead of it given me those values for me, I want it to Return table head Instead of Value.

I was able to come up with this formula "=INDEX($BZ$1:$DV$1,1,MATCH(MAX($BZ5:$DV5),$BZ5:$DV5,0))" but it's not working well.

Please kindly help.

 

[James006]

Hi,
You could test:

=INDEX($BZ$1:$DV$1,,MATCH(MAX($BZ5:$DV5),$BZ5:$DV5,0))

 

[Goldenrules]

Hi,
You could test:

=INDEX($BZ$1:$DV$1,,MATCH(MAX($BZ5:$DV5),$BZ5:$DV5,0))

Thanks for your response I have tried it, it works perfectly well for the highest number in the range. But not working for the 2nd, 3th, 4th, 5th highest. Because there are some same numbers within the range.

 

[James006]

Glad to hear this could help

In your formula you are using Max which is equivalent to Large(yourRange,1)

If you are after the subsequent results, you will need to use Large(yourRange, n )

where n will take the values you need : 2,3,4, etc ...

Hope this clarifies

 

[Goldenrules]

Glad to hear this could help

In your formula you are using Max which is equivalent to Large(yourRange,1)

If you are after the subsequent results, you will need to use Large(yourRange, n )

where n will take the values you need : 2,3,4, etc ...

Hope this clarifies

I have used it countless times, but not working as expected. If you can permit me to share the link to the file, because it's on Google sheet.

 

[James006]

No problem ...

Even if I have never used Google Sheets ...

 

[Joe4]

I have used it countless times, but not working as expected. If you can permit me to share the link to the file, because it's on Google sheet.

Note that while there are many similarities between Excel and Google Sheets, they are NOT the same, and there actually are differences.
As such, if you are posting a question about Google Sheets, pleasing clearly state you are doing so in your original title/question post, and post it to the appropriate forum, "General Discussion & Other Applications".

I have moved this thread for you to that forum.