The second one is used in advanced cases only (such as doing a three-way lookup) which we will cover in one of the examples later in this tutorial.īut if you’re new to this function, just remember the first syntax.īelow is a video that explains how to use the INDEX function INDEX function has 2 syntaxes (just FYI). – (Optional) If array argument is made up of multiple ranges, this number would be used to select the reference from all the ranges.Although this is an optional argument, but if row_num is not provided, it needs to be given. – the column number from which the value is to be fetched.row_num – the row number from which the value is to be fetched.array – a range of cells or an array constant.The fact that you can use it with other functions (hint: MATCH) that can find the row number and the column number makes INDEX an extremely powerful Excel function.īelow is the syntax of the INDEX function: =INDEX (array, row_num, ) After all, in most cases, you are not likely to know the row and column numbers. The INDEX function will use the row number and column number to find a cell in the given range and return the value in it.Īll by itself, INDEX is a very simple function, with no utility. So let me define it in simple words for you. Just like a satellite needs latitude and longitude coordinates, the INDEX function in Excel would need the row and column number to know what cell you’re referring to.Īnd that’s Excel INDEX function in a nut-shell. I quickly did a tìm kiếm for my work location and this is what I got. So despite having a mind-boggling number of lat-long combinations, the satellite would know exactly where to look. The easiest way to understand how Index function works is by thinking of it as a GPS satellite.Īs soon as you tell the satellite the latitude and longitude coordinates, it will know exactly where to go and find that location. INDEX/MATCH is Independent of the Actual Column Position INDEX/MATCH can work with vertical and horizontal ranges INDEX/MATCH can look to the Left (as well as to the right) of the lookup value Let’s Combine Them to Create a Powerhouse (INDEX + MATCH)Įxample 1: A simple Lookup Using INDEX MATCH ComboĮxample 4: Lookup Value From Multiple Column/CriteriaĮxample 5: Get Values from Entire Row/ColumnĮxample 6: Find the Student’s Grade (Approximate Match Technique)Įxample 9: Use INDEX MATCH with Wildcard Characters Understanding Match Type Argument in MATCH Function MATCH Function: Finds the Position baed on a Lookup Value INDEX Function: Finds the Value-Based on Coordinates Now before I show you how the combination of INDEX MATCH is changing the world of analysts and data scientists, let me first introduce you to the individual parts – INDEX and MATCH functions. So I am trying to simplify it using this tutorial. But since people find it difficult, it gets used less. I believe INDEX MATCH is a better option in many cases. Many people find VLOOKUP to be easier to use (and that’s true as well). There are other lookup formulas in Excel – such as VLOOKUP and HLOOKUP and these are great. Note: There are other lookup formulas in Excel – such as I even wrote an article about Index Match Vs VLOOKUP which sparked a little bit of debate (you can test the comments section for some firework).Īnd today, I am writing this article solely focussed on Index Match to show you some simple and advanced scenarios where you can use this powerful formula combo and get the work done. I am a huge fan of INDEX MATCH combo and I have made it pretty clear many times. The amount of work you can get done with a few formulas still surprises me (even after having used Excel for 10+ years).Īnd among all these amazing functions, the INDEX MATCH functions combo stands out. Excel has a lot of functions – about 450+ of them.Īnd many of these are simply awesome.
0 kommentar(er)
