![]() 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |