index match Excel

Copper Contributor

Hello,

 

I have to find a value in column A and display the smallest, second smallest, third smallest,... value of the corresponding row. 

example from screenshot:

if i look for value '2087912' in row 12, i have to get the value '9.85' in my first cell, 20.18 in my second cell and 29.24 in my third cell. 

 

Can anyone help?

Thank you!

 

5 Replies

@finaly 

I guess that's approximately the same as here https://techcommunity.microsoft.com/t5/Excel/excel-formula/m-p/867183#M40459, isn't it?

Hi @Sergei Baklan ,

 

yes correct, that formula worked great for finding the corresponding top row. I've tweaked that a bit so i can now drag it down instead of right.

But now i need to display the actual smallest, second smallest value of the row. I've been trying to make sense of the formula and trying to make it work myself for the purpose of what im looking for now, but cant seem to get it to work :(

I always get an error

 

 

 

@finaly 

Okay, I see. For such ranges

image.png

formula in L2 is

=SMALL(INDEX($C$2:$H$7,MATCH($J$2,$B$2:$B$7,0),0),$K2)

@Sergei Baklan 

cheers! thank you for your help! 

@finaly , you are welcome