Lesson 16: Index and Match functions in excel.

Learn Index and match Functions in Microsoft Excel!

This video is a part of free Excel Training Singapore, where I show you how to use Index and match Functions in excel from the basics and also cover a lot of advanced Features.

After gaining knowledge of various formulas and their uses in excel, those formulas can be used in a combined state as well. Some functions where numerous formulas can be used are GET CLOSEST MATCH, COMPARE LIST and many more.

Description of various formulas in excel –

• COMPARE LIST – COMPARE LIST function is used to tally each and every cell of one list with the other one. The prerequisite for this function to work well is that the data stored must be same in all the cells.
• GET CLOSEST MATCH – GET CLOSEST MATCH function is used to pick the perfect match from the previously mentioned data table for a new table by satisfying the condition given there.
• GET LAST VALUE IN THE LIST – GET LAST VALUE IN THE LIST function is used to fetch the last value from any list. The list need not be arranged in any order as this function takes the help of VLOOKUP formula to fetch the last value. The function takes four input values.

How to use the compare list formula?

This function is used to compare various lists that may be contained in a single worksheet or numerous worksheets. To learn how to work with this function, follow the following mentioned steps –

• Make sure you have same data in both the cells. Start by selecting the cell reference of first cell followed by an equal to sign and then select the cell reference of second cell, which is, B4=C4 in this example
• Close the parenthesis and press enter. This will return either true or false as it is a logical argument. The output so received will be true in this example
• To fill the entire table with logical output, double click on the first output. FALSE will be displayed as output where the contents of two cell aren’t the same.
• To quickly analyze where you have mismatched data, click on filter or use keyboard combination shortcut ctrl +shift +N. To apply filter, click on data, then click on filter and select false. This will display all the cells having false as output

How to use the GET CLOSEST MATCH FUNCTION ?

The GET CLOSEST MATCH function is used to extract the most suited data from a given data for a new table by fulfilling the condition so mentioned. To learn how to use this function, follow the following mentioned steps –

• Prepare a data as in the given example it is employees and their work experience . Prepare another table specifying the data you wish to find from the previous data.
• As in the given example it’s specified that people with 2.2 years of experience are required. To find that, use the INDEX formula
• Start by typing the INDEX formula followed by selecting the entire data range and press f4 to fix that input.
• In the row column, use the MATCH function. Select the value to be found as the look-up value and then select the entire range of data as the look-up array . Hit f4 again to lock this input as well
• To receive the employee name along, keep the column number as one. The output so displayed will be the name having the exact value asked which is jenny in this case.
• If the value so inputted doesn’t exists in the look-up array then an error output will be displayed

How to use the FIND LAST OCCURANCE OF AN ITEM IN THE LIST function?

The find last occurrence of an item in the list function is used to find the last time a value occurred in the list. This function can be understood with the help of example given below –

• Prepare a data having names of employees and the date of last meeting that they chaired. Next to the table, write any employees name and find out when was the last meeting he chaired and if that happens to be very recently, abstain from giving him the next responsibility.
• Let’s take the example of Steve here. To find out when was the last meeting he attended, use the INDEX FUNCTION. Give the input as the entire array of the date of meeting
• To find the last occurrence of Steve, follow along with ROW function. Give the same input as INDEX FUNCTION. Close the brackets and multiply it with a condition which is equating the cell reference of the employee name with all the other employee names
• Insert the max function before ROW function and subtract 3 from the entire formula so that now the values start from row 1.
• Close the brackets and press key combinations ctrl + shift + enter. The output so displayed will be the last occurrence of Steve’s chaired meeting date

Join a Microsoft Excel course in Singapore!

Microsoft excel allows usage of multiple formulas in a single input which Makes working with excel a lot easier and better. To make the most out of it, join a Advanced Excel Training Course in Singapore today!

There a lot of awesome things you can do with a combination of these advanced formulas in Excel.

While most of things can be managed with in-built Excel functions, sometimes you require to use a combination of these formulas to get the work done.

This video we will show 10 examples where you can use advanced excel formulas (including a combination of in-built Excel functions) to achieve the desired result.

we cover the following advanced formula examples in this video:

• Compare Lists, Get Unique List
• Get the Closest Match
• Get Last Value in the List
• Find the Last Occurrence of an Item in a List
• 2 Way and 3 Way Lookup using Index/Match
• Count the Number of Words
• Extract Username from Email Id
• Find the First Monday of the Month
• Extract Data using Drop Down List

Again, the combination of formulas you need to use will depend on what you want to reach, but these advances excel formula examples will give you variety of ideas on how you can mix & match formulas, use the result from one formula as an input for another formula. It also covers array formulas, where you need to use Control + Shift + Enter instead of a simple Enter.