Small function

[businessasusual]

I am having a problem using the small function. I have a list of 5 numbers down column A. I want to find the number in column D based on the 3rd smallest number in A. =+VLOOKUP(SMALL($a1:$a5,3),$a1:$d5,4). This function works sometimes for the 2nd smallest number but not the 1st or 3rd. I am using the EXACT same formula, but changing the 3 above to a 1 or 2. One or two of the answers is correct, but not all 3!! Why!!

 

[Biz]

Try

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">8</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">100</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">400</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">500</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">200</TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">300</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">300</TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">500</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">400</TD><TD> </TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">500</TD><TD> </TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">200</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G1</TD><TD>=VLOOKUP(SMALL($A$1:$A$5,F1),$A$1:$D$5,4,FALSE)</TD></TR><TR><TD>G2</TD><TD>=VLOOKUP(SMALL($A$1:$A$5,F2),$A$1:$D$5,4,FALSE)</TD></TR><TR><TD>G3</TD><TD>=VLOOKUP(SMALL($A$1:$A$5,F3),$A$1:$D$5,4,FALSE)</TD></TR><TR><TD>G4</TD><TD>=VLOOKUP(SMALL($A$1:$A$5,F4),$A$1:$D$5,4,FALSE)</TD></TR><TR><TD>G5</TD><TD>=VLOOKUP(SMALL($A$1:$A$5,F5),$A$1:$D$5,4,FALSE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Biz

 

[AlphaFrog]

Try this...

=VLOOKUP(SMALL($A1:$A5,3),$A1:$D5,4,0)

The zero or False is an argument to find an exact match. Otherwise it will find the closest match and if your data is not sorted in column A, you will get unexpected results.

 

[T. Valko]

I am having a problem using the small function. I have a list of 5 numbers down column A. I want to find the number in column D based on the 3rd smallest number in A. =+VLOOKUP(SMALL($a1:$a5,3),$a1:$d5,4). This function works sometimes for the 2nd smallest number but not the 1st or 3rd. I am using the EXACT same formula, but changing the 3 above to a 1 or 2. One or two of the answers is correct, but not all 3!! Why!!

Use the 4th argument in VLOOKUP and set it to 0 or FALSE.

Also, you don't need the + sign:

=VLOOKUP(SMALL($A1:$A5,3),$A1:$D5,4,0)
=VLOOKUP(SMALL($A1:$A5,3),$A1:$D5,4,FALSE)

 

[businessasusual]

Thank you! I thought I tried that, but must have missed something. You are my hero!