Sep 20 2019 05:12 AM
Hi,
I need a formula that looks for a value in column A, finds the smallest value of that row and displays the corresponding value of a row on the top of the sheet (row 3 in my case) where the smallest value has been found. Also would like to find the second and third smallest value and display the value of row 3.
For example: I need to lookup X in the first column, find the smallest value in that row (1) and display the value on the row on top of that column (D) in one cell and in two other cells i need the second smallest value for X to display C, third smallest for X to display E.
A B C D E F
Z 6 8 9 5 2
X 7 2 1 3 7
Y 9 2 6 1 6
Sep 20 2019 05:33 AM
If located like this
that could be in I1
=INDEX($B$1:$F$1,MATCH(SMALL(INDEX($B:$F,MATCH($H$1,$A:$A,0),0),COLUMN()-COLUMN($H$1)),INDEX($B:$F,MATCH($H$1,$A:$A,0),0),0))
and drag to the right