Advance Formula | Learn how to use INDEX + Match formula in Excel | Dashboard Automation Technic | 1

Advance Formula | Learn how to use INDEX + Match formula in Excel | Dashboard Automation Technic | 1

@AkshVerma
@dhruvrathee
@KhanSirPatnaKhanGs
@NetflixIndiaOfficial
@chandrecords
@LuxuryShaukOfficial
@RealMusic1
@khangsresearchcentre1685
@wifistudy2

The INDEX Function
The INDEX function in Excel is fantastically flexible and powerful, and you'll find it in a huge number of Excel formulas, especially advanced formulas. But what does INDEX actually do? In a nutshell, INDEX retrieves the value at a given location in a range. For example, let's say you have a table of planets in our solar system (see below), and you want to get the name of the 4th planet, Mars, with a formula

Excel INDEX and MATCH functions - the basics
Since the aim of this tutorial is to demonstrate an alternative way to do a vlookup in Excel by using a combination of INDEX and MATCH functions, we won't dwell much on their syntax and uses. We will cover only the minimum necessary for understanding the general idea and then take an in-depth look at formula examples that reveal all the advantages of using INDEX MATCH instead of VLOOKUP.

INDEX function - syntax and usage
The Excel INDEX function returns a value in an array based on the row and column numbers you specify. The syntax of the INDEX function is straightforward:

INDEX(array, row_num, [column_num])
Here is a very simple explanation of each parameter:

array - a range of cells that you want to return a value from.
row_num - the row number in array from which you want to return a value. If omitted, the column_num is required.
column_num - the column number in array from which you want to return a value. If omitted, row_num is required.
For more information, please see Excel INDEX function.

And here is an example of the INDEX formula in its simplest form:

=INDEX(A1:C10,2,3)

The formula searches in cells A1 through C10 and returns a value of the cell in the 2nd row and the 3rd column, i.e. cell C2.

Very easy, right? However, when working with real data you would hardly ever know which row and column you want, that's where the MATCH function comes in handy.

MATCH function - syntax and usage
The Excel MATCH function searches for a lookup value in a range of cells and returns the relative position of that value in the range.

The syntax of the MATCH function is as follows:

MATCH(lookup_value, lookup_array, [match_type])
lookup_value - the number or text value you are looking for.
lookup_array - a range of cells being searched.
match_type - specifies whether to return an exact match or the nearest match:
1 or omitted - finds the largest value that is less than or equal to the lookup value. Requires sorting the lookup array in ascending order.
0 - finds the first value that is exactly equal to the lookup value. In the INDEX / MATCH combination, you almost always need an exact match, so you set the third argument of your MATCH function to 0.
-1 - finds the smallest value that is greater than or equal to lookup_value. Requires sorting the lookup array in descending order.
For example, if the range B1:B3 contains the values "New-York", "Paris", "London", the below formula returns the number 3, because "London" is the third entry in the range:

=MATCH("London",B1:B3,0)

For more information, please see Excel MATCH function.

At first sight, the usefulness of the MATCH function may seem questionable. Who cares about the position of a value in a range? What we do want to know is the value itself.

Let me remind you that the relative position of the lookup value (i.e. row and column numbers) is exactly what you need to supply to the row_num and column_num arguments of the INDEX function. As you remember, Excel INDEX can find the value at the juncture of a given row and column, but it cannot determine which exactly row and column you want.

How to use INDEX MATCH function in Excel
Now that you know the basics, I believe it has already started making sense how MATCH and INDEX work together. In a nutshell, INDEX finds the lookup value by column and row numbers, and MATCH provides those numbers. That's it!

For vertical lookup, you use the MATCH function only to determine the row number and supply the column range directly to INDEX:

INDEX (column to return a value from, MATCH (lookup value, column to look up against, 0))

Видео Advance Formula | Learn how to use INDEX + Match formula in Excel | Dashboard Automation Technic | 1 канала Nahar's Computer