When to use VLOOKUP and INDEX + MATCH in Excel
Did you know that VLOOKUP and the combination of INDEX and MATCH in Excel have the same function?
VLOOKUP is much simpler, and it’s one of the first formulas that you learn on excel.
On the other hand, INDEX + MATCH is a combination of complex formulas that is a bit more dynamic.
VLOOKUP or INDEX + MATCH?
Even though VLOOKUP is much easier to set, it can become hard working over time for these two reasons. First one is because the data you’re looking for must always be on the left side of the information to be retrieved by the formula. The second reason is that it doesn’t resize after you increment the table.
It means that you would be constantly resizing the table and the formula, being careful to not crash the VLOOKUP
So, despite the MATCH + INDEX combination being more complex, it ends up being the best option in Excel, in case you need to update the table constantly.
What is MATCH and INDEX?
NDEX is a formula that pulls the value of a specific cell which you selected. Its notation is:
=INDEX(table selection;row number;column number)
With INDEX, you search for a specific value in a row or a column and it brings the row or column ID. Its notation is:
=MATCH(“Name”;chosen column;0)
Being the “name” an element that you’re looking for, and “0”means it can be out of alphabetical order.
How to combine INDEX and MATCH?
To combine INDEX and MATCH to get something similar to VLOOKUP you can use:
=INDEX(select the table;MATCH(the value you want to search;the column where you’ll search;0);MATCH (what you want to find; the row where you’ll search;0)
Excel Disadvantages
Excel helps a lot, but it’s still not the idea for data reliability. After all, it’s not a database. Excel is more akin to a matrix where you type text and other information, it was created to be that, rows and columns combined, not a database.
For example, with VLOOKUP, you’re just copying information around because you cannot truly link one information to the other.
Jestor
With jestor you actually have a record and can connect data. With it, you don’t have to type in formulas or duplicate data, which is usually faster and more organized.
Check out how simple it is to create a table on jestor:
1º) Open jestor. Go to the “+” icon on the left menu, choose the option “table”, name it and click on “create”. Then add the same fields as you would create on Excel by clicking on the header name with a “+”.
2º) You can now fill in the columns, and unlike Excel, you’ll really have connected information.
You can also create other tables and link them to each other, by assigning a task to the project, for example. And with just a few clicks, you can find all the information about the project without using formulas.