SMALL function – INDEX MATCH
The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that if you enter 0 (zero) in the row or column argument. The SMALL function then calculates the k-th smallest value of these three values.
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Allow me to explain the formula, the MATCH function returns the location of a specified value in an array och cell range.
MATCH($C$10, $B$3:$B$7, 0)
becomes
MATCH("D",{"B";"E";"D";"C";"A"},0)
Value D is found in position 3 in this array: {"B";"E";"D";"C";"A"}
INDEX($C$3:$E$7, MATCH($C$10, $B$3:$B$7, 0), 0)
becomes
INDEX($C$3:$E$7, 3, 0)
The INDEX function then returns all values on relative row 3 in this cell range $C$3:$E$7: {590, 830, 280}
SMALL(INDEX($C$3:$E$7, MATCH($C$10, $B$3:$B$7, 0), 0), ROWS($A$1:A1))
becomes
SMALL({590, 830, 280}, 1)
and returns 280 in cell C11.
You can also use the SMALL function to match multiple values, make sure you read this post: 5 easy ways to VLOOKUP and return multiple values
Download Excel *.xlsx file
SMALL function - INDEX - MATCH.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
INDEX MATCH – multiple results
The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]
Lookup multiple values across columns and return a single value
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]
INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]
INDEX and MATCH – multiple criteria and multiple results
The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
SMALL function with duplicates
The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers. […]
The array formula in column E, shown in above picture sorts text values from column B. The Length columns prove […]
How to ignore zeros using the SMALL function
The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros. Weekly Blog EMAIL […]
2 Responses to “SMALL function – INDEX MATCH”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi,
Is there a way to do this same idea, but when your data is organized vertically? You appear to be the only person that I've seen solve my problem - which is, bring back all results based upon a specific date AND sort that data dynamically (smallest number to largest) in a separate tab without having to adjust the sorting manually. I've downloaded your file and tried to adjust the formula for my purposes, but am missing something when I do.
Report Tab:
N3 = My manually entered date field to match to
A6 = The first field where the formula & smallest results would go (Then A7, etc).
Data Tab: Fields may contain data, be empty and/or contain a formula
Column C = Date field to compare with N3 (Named range of ArrivalDate (C3 to C500))
Column A = Number to sort by. (Named range of SortCode (A3 to A500))
I've got it working today based upon the smallest row number being returned, but really want it based upon the small value first.
My formula now is: =IFERROR(INDEX(SortCode,SMALL(IF(ArrivalDate=$N$3,ROW(SortCode)),ROW(1:1))-1,),"")
Any suggestions/help are welcome. My first time using Index functionality.
Thank you so much!
Todd
Todd,
try this array formula:
=IFERROR(INDEX(SortCode,MATCH(SMALL(IF(ArrivalDate=$N$3,SortCode),ROWS($A$1:A1)),SortCode,0)),"")