INDEX with MATCH function or VLOOKUP can be used to extract the matching record but both functions will stop at the first occurrence of lookup value. What if our requirement is to extract all the matching record of lookup value, then we have to get help from AGGREGATE or SMALL function. Both of this function coordinates well with INDEX function just like MATCH. To get the glimpse of our requirement, please look into below pic.
Range B4:F17 contains sample sales data with Product, Date, Unit Price, Quantity and Sales amount. In the cell K2, Products 'A','B','C','D' provided in Drop down.
Our requirement is to extract the records from B4:F17 matching with K2 cell.
We can get the multiple matches in different methods in excel, but here we are going to discuss INDEX and SMALL function method.
Lets take a look at the formula in I5 cell,
=INDEX(B$5:B$17,SMALL(IF($B$5:$B$17=$K$2,ROW($B$5:$B$17)-ROW($B$5)+1),ROWS($I$5:I5)))
If we evaluate the value_if_true argument in IF function,
it delivers the sequence of numbers from 1 to 13 which will act as record number of our data.
Let me explain you about how this construction works, The ROW function will deliver the Row number of given reference, If we feed B5 cell as the reference in ROW function, it will deliver 5. If we feed B5:B17 as the reference it will deliver the row numbers of that range, So the output will look like below.
If we detect the row of B5 which is 5, then the sequence will start from 0 and so we are adding 1 to it. This is the robust construction for sequence of numbers, so it will work even if we delete the rows.
Now if we evaluate the logical_test argument in IF function,
Bonus Tip : For that, click after IF in formula bar, In the IntelliSense, select logical_test using mouse, you can see that whole logical_test is getting selected in formula, now press F9 key to evaluate. Remember to press ESC or Ctrl + Z to came out of this evaluation, otherwise evaluated portion will get hard coded.
Let's get into the topic,
Each cell in the range $B$5:$B$17 is checked, are you equal to $K$2 cell value which is 'A' or not ? If yes then TRUE is delivered else FALSE is delivered.
For each occurrence of TRUE value in logical_test argument, value_if_true argument will get evaluated and it will pick the relative position, So in our case, if we evaluate the array argument in SMALL function it will look like below.
If we deconstruct the SMALL function, it has below arguments,
=SMALL(array,k)
This function will deliver k-th small item in the given array. Array argument in SMALL function is programmed to ignore Boolean values and so it will ignore FALSE.
If you look at the data, the relative position of the records corresponds to 'A' are 1, 2 and 7.
So, as of now we are travelling in right direction. To extract 1st small in that array, we can use ROWS function with expandable range,
First reference ($I$5) in rows function is locked in both row and column, it is absolute reference, It will not change as we drag the formula down or right. 2nd reference (I5) is not locked in both row and column, it is relative reference.
I5:I5 how many rows are there ? only one and so the rows function will deliver 1, then small function will pick the first small of the array which is 1, If we drag the formula down, 2nd reference will change as I6, How many rows are there in the range($I$5:I6)? 2 rows are there.
and so rows function will deliver 2 and the small function will pick 2nd small in the array and so on.
Then INDEX function will deliver the results from B$5:B$17, based on the output of SMALL function which are 1, 2 and 7. So, INDEX function will pick ranges B5,B6 and B11.
If we drag whole formula in right to J5 cell, then B$5:B$17 will become C$5:C$17 and the small function will help us to get the dates from "C" Column for matched records of product 'A', In the same way if we drag the formula till "M" Column, the result will look like below.
Comments