#Words of wonder level 324 how to

The row number goes directly to the row_num argument of INDEX instructing it to return a value from that row.The MATCH function searches for the lookup value 'Japan' in the range A2:A10, and returns the number 3, because 'Japan' is third in the lookup array.Now, let's analyze what each component of this formula actually does:

To find the population of a certain capital, say the capital of Japan, use the following INDEX MATCH formula:

Suppose you have a list of national capitals and their population: Still having difficulties to figure that out? It might be easier to understand from an example. INDEX ( column to return a value from, MATCH ( lookup value, column to look up against, 0)) That's it!įor vertical lookup, you use the MATCH function only to determine the row number and supply the column range directly to INDEX: In a nutshell, INDEX finds the lookup value by column and row numbers, and MATCH provides those numbers. Now that you know the basics, I believe it has already started making sense how MATCH and INDEX work together. 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. row and column numbers) is exactly what you need to supply to the row_num and column_num arguments of the INDEX function. Let me remind you that the relative position of the lookup value (i.e. Who cares about the position of a value in a range? What we do want to know is the value itself. Requires sorting the lookup array in descending order.įor 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:įor more information, please see Excel MATCH function.Īt first sight, the usefulness of the MATCH function may seem questionable. -1 - finds the smallest value that is greater than or equal to 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. 0 - finds the first value that is exactly equal to the lookup value.Requires sorting the lookup array in ascending order. 1 or omitted - finds the largest value that is less than or equal to the lookup value.match_type - specifies whether to return an exact match or the nearest match:.lookup_array - a range of cells being searched.lookup_value - the number or text value you are looking for.The syntax of the MATCH function is as follows: 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. 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. 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. If omitted, row_num is required.įor more information, please see Excel INDEX function.Īnd here is an example of the INDEX formula in its simplest form: column_num - the column number in array from which you want to return a value.row_num - the row number in array from which you want to return a value.array - a range of cells that you want to return a value from.Here is a very simple explanation of each parameter: The syntax of the INDEX function is straightforward:

The Excel INDEX function returns a value in an array based on the row and column numbers you specify. 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. 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.

INDEX MATCH formula with multiple criteriaĮxcel INDEX and MATCH functions - the basics.

INDEX MATCH MATCH to lookup in rows and columns.

The basics of INDEX and MATCH functions.

On the other hand, the INDEX MATCH combination is more flexible and has many awesome features that make it superior to VLOOKUP in many respects. Because VLOOKUP has numerous limitations that can prevent you from getting the desired result in many situations. 'What do I need that for?' you may wonder. And now, I will try if not to talk you out of using VLOOKUP, then at least show you an alternative way to do a vertical lookup in Excel. In a couple of recent articles, we made a good effort to explain the basics of VLOOKUP function to beginners and provide more complex VLOOKUP formula examples to power users.

#Words of wonder level 324 how to

This tutorial shows how to use INDEX and MATCH in Excel and how it is better than VLOOKUP.