Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH

Quick lookup of data in Excel tables: Replace VLOOKUP with INDEX and MATCH. Do you know yet? Let's TipsMake.com find out in this article offline!

1. How to print a selection in Microsoft Excel
2. Instructions on how to set a password to protect the Excel file safely
3. Instructions for creating interactive charts in Excel with INDEX function

Are you still using Vlookup function in Excel to find everything in a spreadsheet or range by row? This is how the INDEX function and MATCH function can give you a better solution.

Excel spreadsheets are a great way to synthesize and organize a large amount of information. However, scanning that data to find personal records can quickly consume your time. Fortunately, there are many ways to speed up that process.

Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH Picture 1

VLOOKUP is currently the approach of many users when it comes to this type of work. It is fast and relatively simple, but not as powerful as other alternatives. Using the INDEX function and MATCH function, users can avoid some silly mistakes that often arise when using the VLOOKUP function. In addition, if you are dealing with a particularly large spreadsheet, you will find that your search operations are done much faster.

Here's a good reason why you should learn how to use the INDEX and MATCH functions , not just stick with VLOOKUP.

How to use Vlookup function

First, we'll briefly discuss how the VLOOKUP function works. Below is a table named 'Stock - source', ID number and prices of different items of clothing.

Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH Picture 2

When I enter the Stock ID card into cell C10 , cell C11 will automatically update the corresponding price. This is because it checks the string in cell C10 for the cells in the specified range, A1: C8 . Number 3 in the formula tells Excel we are looking for a box in the third column, because we are looking for "Price - Price". Finally, adding FALSE ensures that our formula will return only the exact same values, not the approximate results.

This method works well, but it is not ideal if you are planning to edit the spreadsheet the next day. For example, if we add a new column to the spreadsheet, we run the risk of breaking the formula because the price column is no longer the third column from the left.

By using a different method, we can eliminate the need to count how many Stock ID and Price separation columns are, eliminating some potential user errors . This process is also much more efficient in terms of processing power, which can speed up if you are working with huge data sets.

How to use the INDEX function and MATCH function

We can avoid the annoying mistakes that VLOOKUP can cause by using the INDEX function and MATCH function . This is a bit more complicated method, but it's really not too difficult to grasp when we divide the process into its components.

We will use the INDEX function to specify a specific set of cells, then we will use the MATCH function to find the right cell in that group.

How to use INDEX function in Excel

Here is a simple way to implement the INDEX function function.

Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH Picture 3

As you can see, we have specified the range of cells containing the price for each item, C2: C8 . In this case, we know that the cell we're looking for is in the seventh row or less. However, we can replace this information with a MATCH function, which allows searching a special cell by entering the Stock ID in cell C10 .

How to use the Match function in Excel

This is how we use the MATCH function in the spreadsheet.

Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH Picture 4

The MATCH function allows us to specify a string to look for, in this case anything inside the cell C10 . We then describe a range to search and add a zero at the end to determine that we only want to find the correct answers. This returns the position of the cell, where the cell C12 is located , telling us that the Stock ID matches the string BE99 in the fourth cell down, ie A5.

How to combine the INDEX function and MATCH function

In order for the INDEX and MATCH functions to work simultaneously, we only need to give our row reference from the INDEX function formula with the MATCH function formula.

Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH Picture 5

As you can see, building our recipe is really not that complicated . It is only a matter of organizing the MATCH function inside the INDEX function and making sure that both elements are attached to the correct cells and ranges. Currently, our spreadsheet is set up in this way, we can change the way it is set without violating the formula.

Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH Picture 6

It may take longer to implement the INDEX function and MATCH function than using VLOOKUP, but the results we will have a more flexible document with fewer errors . If your spreadsheet relies on some lookup functions, you can save yourself some trouble later by removing the VLOOKUP function and learning how to use the INDEX and MATCH functions properly.

Not easy is always the best!

Excel is a very complex software and deep understanding of functions is often quite "scary". One thing to talk about to solve simple problems, often a more complex method can bring great benefits in the long run.

There is nothing wrong with using VLOOKUP to do this. However, the INDEX function and the MATCH function reduce the user's error impact and do not require further editing when you make structural changes to your spreadsheet . Using them in a harmony state is just a little more advanced than performing VLOOKUP functions, but it gives users some great advantages.

The more useful Excel becomes, the more you learn about it, so it's always beneficial to learn a new approach and understand new functions.Do not overslept on victory! Take the time to expand your knowledge.

Are you struggling to implement the INDEX and MATCH functions into your spreadsheet? Or do you have a good tip on how to make the most of these functions to share?Either way, why don't you join the conversation in our comment section below?

Refer to some related articles:

1. How to create tables, insert tables in Excel
2. How to reopen Word, Excel or PowerPoint files when closed but not saved yet?
3. Use VLOOKUP to join two Excel tables together

Having fun!