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
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
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies
Help with minutes/seconds to seconds.
SteveMorley in Excel on
1 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies