On the other hand, some users find INDEX MATCH as difficult to understand due to its combination of two formulae that seem complex.Д©. It is easy to understand as well as implement in comparison with INDEX MATCH due to the simple nature it has. This is the only fact that goes in the way of VLOOKUP. See the screenshot below for better visualization:ДЁ. On the other hand, INDEX MATCH is smart enough to find out the actual value associated with the column when we drag the formula across rows/columns. It provides the same result as the first lookup result because we are using static references. VLOOKUP is very poor when we try to drag the formula across rows or columns with the same reference value.

On the other side of the coin, the INDEX MATCH does not have any restrictions for the lookup value.Д§. If any lookup value is exceeding 255 characters, the VLOOKUP function will produce an error. VLOOKUP has size restrictions for lookup value which canвЂ™t exceed 255 characters. In short, INDEX MATCH does the work of both VLOOKUP and HLOOKUP in a single formula.Д¦. Therefore, it can lookup values through both. INDEX MATCH, on the other hand, is flexible to move through columns as well as rows. Since it can only be used for vertical lookups, it automatically becomes restricted not to be used over horizontal lines (for this, we use HLOOKUP). V at the start of the formula stands for vertical. VLOOKUP formula can only look up the values across vertical lines, i.e. It makes this function more flexible compared to VLOOKUP.ДҐ. INDEX MATCH does not have such restrictions it can be used to lookup the values from left to the right as well as from right to left. VLOOKUP can only be used when you have the data from left to right, or in other words, it can only be used when the column associated with lookup value the first one in the table array. Whereas, in INDEX MATCH, though we have added the column, the function was flexible enough to capture the right salary value concerning the lookup value.Д¤. While using VLOOKUP, we are failing to capture the accurate salary value for the lookup value. You can see the output of two of the functions after we inserted a new column named Location in the table array. It does not alter the result as we are using dynamic ranges within the formula. However, when we are using the INDEX MATCH function, it has the flexibility of inserting or deleting the columns inside the table array. Inserting or deleting a column in the table array under the VLOOKUP function provides misleading results as it uses the static data references.

See the below two images for your references.ДЈ. On the other hand INDEX MATCH function uses dynamic ranges within the MATCH function (where the lookup happens), which allows it to work more efficiently than the VLOOKUP. VLOOKUP uses the static data references to look up the values in an array which means the column references are denoted by numbers such as 1, 2, 3,вЂ¦ etc. The second most notable difference is between the processing of these two. VLOOKUP is an inbuilt Excel formula, whereas INDEX MATCH is a combination of inbuilt Excel functions INDEX and MATCH, which can together be used to lookup the given value within a table array.Дў. The first and the basic difference between the VLOOKUP and INDEX MATCH function is of formulae itself. Let us discuss some of the major key differences between VLOOKUP vs INDEX MATCH.ДЎ.

Key Differences between VLOOKUP and INDEX MATCH Formula Excel functions, formula, charts, formatting creating excel dashboard & others

Vlookup index match different worksheet