Excel Index & Small and Index & Large Function in an Array Formula

Показать описание

In this video, I will show you two problems that can be solved by combining INDEX() & SMALL() function and INDEX() & LARGE().

The first example aims to group all the scores per color on a per column format (from a per row data format). The solution for this is to use put and IF() statement in a SMALL() function where the value if true returns the row number of the score through the ROW() and MIN() functions. While for the “k” in the SMALL(), an expandable cells is created using the ROWS() function. The answer from SMALL() formula will serve as the row number of the INDEX().

The second and final example aims to get the top company name based on the highest revenue. The example includes a tie. The solution is to use the LARGE() function with an IF() statement testing if each of the revenue is the maximum using the MAX() formula. For the “k” in the LARGE() the same trick of creating expandable cells as in the SMALL() function using the ROWS() formula. The answer from LARGE() formula will serve as the row number of the INDEX().

Link to download the sample file:

Link on a youtube video for Get the top 3 unique score within a List in Excel:

Thanks for your time and I hope you learn something from it. 😊

Help me to reach my target audience by sharing my videos with the right people. 😊

Don’t forget to Subscribe! 😊