Excel Examples: Advanced Index

In this video tutorial we are going to use some advance functions to fulfil our requirement. Target is to fetch all the amount of any sales person one by one. If the requirement will be to sum sales amount of any sales person, it will be easy. It can be done by SUMIF function, without much difficult. But here to fulfil the requiremet, is a little bit difficult. We have used some functions like INDEX, SMALL, IF, ROW, ROWS, IFERROR to achieve it. The array functions have been used to complete it. Note that, you have to press SHIFT + CTLR + ENTER instead of pressing only ENTER key. In the above example, the IF function festches the row number of sales person, and the SMALL function is used to arrange the row numbers form small to large. The INDEX function is used to fetch the sales amount of sales person from row. Finally, the IFERROR function is used to convert the sale value to blank from error value.