Explanation
Explanation Part 1 – IF() and ROW()
IF() function is to test whether the ID in the ID column matches with the one located in the cell E2. Wherever it matches, it will return the ROW number of the matched ID, else empty string.
But if your eyes are sharp enough, you will be able to learn about the output area. The first few output from the Function Argument dialog box is display eight of => “” and the starting from the 9th the output is displayed as number.
Among the IDs in the column of ID from “paid” table, starting from the 9th , the IF function has found the match with the reference E2. Hence when you look at the output are as highlighted in red in the figure above, it displays number (the row number) instead of. You must be curious why should I deduct 1 from the ROW() function. Simply because we do not need to know which row the match is from, but we want to start the index counting from 1. If without deducting 1 from the ROW function, the Index will start from 2.
The rest of the output are FALSE because those are not match compare to the reference in E2, hence “” is being returned.
Forgot to mentioned, this is an ARRAY formula. So once you’ve finished with the formula, don’t forget to CRTL + SHIFT + ENTER.
Explanation Part 2 –SMALL() and ROWS()
In order for us to retrieve the index of the first match, we will be needing the function SMALL(). With this function, we will be able to extract the first smallest number from the output from the IF() function.
So now I will be using SMALL() function to nest the IF function within it. Then we will need to extract the 2nd smallest the 3rd smallest etc. <= the 1st, 2nd and Nth amount of the we will be using ROWS() function to return the cell count. To get the cell count, ROWS() function will be used.
Explanation Part 3 –Index()
Extracting or retrieving the value from Date column using Index function. Index function is known to retrieve value from a series of an array based of the given position number. Since part 1 to part 2, we’ve been busy retrieving the position number of the ID, now it’s time to put it to good use. Check out the .gif below.
Explanation Part 4 –IFERROR()
If you do realize that there are a few error output at the bottom part, simply because when the Nth smallest number is not found in the array, #NUM! will be returned. Another explanation will be “Whenever, the Nth is exceeded the amount of count of number stated in the array the same output will be returned.”
IFERROR() function will be use to surround the nested function from prt1 and prt 2, to return a different output instead of #NUM!. What we want is return empty string “”. This is how it goes..
Explanation Part 5 –Copy and Paste the formula to Amt Column
We’re almost there. To complete the whole search table, we will need to copy the formula from Date column to the Amt column. There are many ways that you can copy a formula from one location to another. Below, I show you different ways to copy your formula. You’re not going to just copy and paste the formula, we will be doing a little adjustment which is changing the array in the Index() function.
Ctrl C Ctrl V
Ctrl C the cell contains the formula and Ctrl V at the destination.
Ctrl R
Ctrl R is another favourite option of mine whenever I would like to copy the formula towards to the adjacent column (which is to the right). In order to do that, you will need to highlight the cell that contains the formula, and then the destination (cell/ range) that you would like to copy to. Finally apply your keystroke Ctrl R
Paste as Fx
Pasting as a formula is also another good option. The best thing about this option is, it will not paste the formatting from the cell before to the destination cell, but just the formula.
