excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-867154%22%20slang%3D%22en-US%22%3Eexcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-867154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20need%20a%20formula%20that%20looks%20for%20a%20value%20in%20column%20A%2C%20finds%20the%20smallest%20value%20of%20that%20row%20and%20displays%20the%20corresponding%20value%20of%20a%20row%20on%20the%20top%20of%20the%20sheet%20(row%203%20in%20my%20case)%20where%20the%20smallest%20value%20has%20been%20found.%20Also%20would%20like%20to%20find%20the%20second%20and%20third%20smallest%20value%20and%20display%20the%20value%20of%20row%203.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20example%3A%20I%20need%20to%20lookup%20X%20in%20the%20first%20column%2C%20find%20the%20smallest%20value%20in%20that%20row%20(1)%20and%20display%20the%20value%20on%20the%20row%20on%20top%20of%20that%20column%20(D)%20in%20one%20cell%20and%20in%20two%20other%20cells%20i%20need%20the%20second%20smallest%20value%20for%20X%20to%20display%20C%2C%20third%20smallest%20for%20X%20to%20display%20E.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%26nbsp%3B%20B%26nbsp%3B%20C%26nbsp%3B%20D%26nbsp%3B%20E%26nbsp%3B%20F%3C%2FP%3E%3CP%3EZ%26nbsp%3B%206%26nbsp%3B%20%26nbsp%3B8%26nbsp%3B%209%26nbsp%3B%20%26nbsp%3B5%26nbsp%3B%202%3C%2FP%3E%3CP%3EX%26nbsp%3B%207%26nbsp%3B%20%26nbsp%3B2%26nbsp%3B%201%26nbsp%3B%20%26nbsp%3B3%26nbsp%3B%207%3C%2FP%3E%3CP%3EY%26nbsp%3B%209%26nbsp%3B%20%26nbsp%3B2%26nbsp%3B%206%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%206%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-867154%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-867183%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-867183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412417%22%20target%3D%22_blank%22%3E%40finaly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20located%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20380px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132770iC450659DF8E49E42%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethat%20could%20be%20in%20I1%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24B%241%3A%24F%241%2CMATCH(SMALL(INDEX(%24B%3A%24F%2CMATCH(%24H%241%2C%24A%3A%24A%2C0)%2C0)%2CCOLUMN()-COLUMN(%24H%241))%2CINDEX(%24B%3A%24F%2CMATCH(%24H%241%2C%24A%3A%24A%2C0)%2C0)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20to%20the%20right%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

1 Reply
Highlighted

@finaly 

If located like this

image.png

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