Home

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
finaly
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

@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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies