Ten Alternative Excel Methods To VLOOKUP

Things are heating up on Instagram where I share my entrepreneur story, what consultant life is like, and give a behind the scenes look at the Business Partnering Institute. You can go follow me there by clicking here.

This is the channel "Here's the Future of FP&A and Business Partnering" with more than 11,000 subscribers. You can subscribe too and receive my weekly blog directly in your inbox.

This article is co-written by Serdar Barbaros Ozsoy and Anders Liu-Lindberg

Picking the right formula to solve any given problem in Excel is quite hard. Not because it’s complex but because we learn a set of base formulas from the beginning and find it very hard to move on from them when presented with a better alternative. That’s why Serdar and I have set out to be formula busters moving you away from simple yet poor solutions to find better yet still simple solutions!

Last week, we discussed the best way to achieve our goal instead of the flawed formula VLOOKUP: the answer was XLOOKUP. This week, we are going to discuss 10 other alternatives to VLOOKUP. Some of these alternatives are superior to others in terms of calculation and speed, whilst some are not recommended. In terms of complexity, we recommend that you choose simpler and faster solutions. 

The ten alternatives 

We’ll start with most often promoted alternative (at least until XLOOKUP was launched) INDEX-MATCH and then go through each alternative one by one. 

1)   INDEX-MATCH: If you don’t have an Office 365 subscription, INDEX-MATCH is your best alternative to VLOOKUP. INDEX formula provides you with the exact location of a cell in a range. By nesting the MATCH formula in INDEX, you can replace VLOOKUP in a much more robust way. 

INDEX will give you the exact value of the cell in a known position. In the following example, you will get the value of the cell in the 4th row and 2nd column in A1:B5 range.

MATCH will give you the position of a lookup value in a range. In the same example, when you look for the position of “Mars” in A1:A5 range, it will give you 4 since it is in the fourth row in the range. 0 is for an exact match that finds the first value exactly equal to the lookup value.

Following the same logic, you can also use MATCH to find the location of a match at the row level. 

Using the combination of both functions, you can look up and return the value of a cell in a range based on vertical (row-level) and horizontal (column-level) criteria. This is how it looks in the table format for our inventory example:

2)   FILTER: (Office 365 Only) FILTER is another dynamic array introduced by Microsoft for Office365 subscribers very recently. It is a simple yet powerful formula to filter ranges based on criteria.

3)   NESTED XLOOKUP: This formula is almost identical to INDEX-MATCH. You can nest two XLOOKUP results to find a match using both vertical and horizontal criteria.

4)   POWER QUERY:  By using Power Query’s Merge Table functionality, you can merge the Inventory Table to Lookup table and populate the final data to an Excel sheet as a Table or a Pivot Table.

5)   POWER PIVOT - Data Model: You can add your new Power Query table into your data model and create a Pivot Table. You need to create relations between your tables first in your data model. You can create a PivotTable from Power Pivot by taking fields from different tables. 

6)   POWER PIVOT - RELATED You can use RELATED function (it is a DAX function used in Power Pivot) to create a new column in Power Pivot and create a PivotTable. Alternative #5 is better than Alternative #6 because it does not require more data storage. Alternative #6 stores more data due to the additional column, which makes the file larger. This may pose a problem for big datasets.

7)   LOOKUP-1: Lookup formula will look for an approximate match in one column or one row range. We can use lookup as an alternative to bring the product ID. The shortcoming of the LOOKUP formula is that it only looks for an approximate match. If your table isn't sorted in ascending order, you might end up with the wrong match.

8)   LOOKUP-2: Another variation of lookup formula above Lookup will calculate an array of #DIV/0!'s and 1's based on the criteria and will bring the closest match to 2 as the result.

9)   OFFSET-INDEX MATCH: There are many alternatives to VLOOKUP, but you should try to avoid volatile functions like OFFSET. We don’t recommend using volatile functions because these functions trigger recalculation every time a cell is changed and when rows and columns are added. You might not recognize the impact of using a volatile function in small spreadsheets. However, you will experience issues in large spreadsheets. Furthermore, the following formula is very complex and hard to read and write. In terms of spreadsheet management and continuity of your reporting tool, you should try to avoid complex and volatile formulas as much as possible. Even a VLOOKUP formula with dynamic columns is better than OFFSET-INDEX.

10) INDIRECT ADDRESS: Indirect is another volatile function, and you can mix it with multiple formulas such as ROW, COLUMN, MATCH, and MIN to come up with a lookup value. You should try to avoid volatile formulas due to the reasons illustrated in #Alternative 9. This formula is very complex and slow. Imagine that you decided to use this formula as an alternative to VLOOKUP. You must document how this formula works for your back-up who should have above average Excel skills to maintain this formula. Will it be worth taking the risk and effort using Alternative #9 and #10 since there are better alternatives out there?

In total, we presented you with 12 ways to solve the Retail Inventory Review case. You can find more ways to solve this problem, such as creating your own lookup function using VBA.  

Most Excel users would use VLOOKUP. We suggest XLOOKUP as the best solution (INDEX-MATCH for non-O365 users), but as you can see, there are many ways to get to the right answer. 

How many of you are using the full power of Excel? 

In these two articles, we’ve just addressed one classic formula i.e. VLOOKUP. There are many others and for each of them, there are often better alternatives. We’ll keep solving cases like this for you in the coming months.

Meanwhile, if you’d like the Excel sheet where you can see all the details from the examples given then don’t hesitate to reach out to us.

Also, if you have any formulas, you’d like us to help you improve then just post them in the comments and we’ll put them in the pipeline. We know only a fraction of you are using the full power of Excel but there’s no reason not to do it. Let’s help you get there by indulging in better alternatives for classic Excel formulas!

This was the eleventh article in the Excel series teaching you how to increase your productivity working in Excel. You can find previous articles below.

Are You Ready To Power Up Your Excel Game?

Here Are The Top 7 Mistakes You Make In Excel. Should We Try And Fix Them?

I Use Excel For Everything But Should I?

Excel Is Like The Grown-Up Version Of A Kindergarten Sandbox

15 Must-Have Habits To Master Excel

A Good Excel Sheet Never Starts At A1

The Simple Functions And Techniques That'll Save Your Excel Sheet

Pssst, Do You Want Some Excel Cheat Codes?

Here Are The Top Excel Features For Any Modeller To Use!

Excel Formula Challenge: How To Do A Retail Inventory Review With XLOOKUP

Continue reading below for more articles about how digital is impacting Finance.

Inevitable Retraining Of Finance Professionals Is On The Horizon

Why The Digital Revolution Hasn’t Caught Onto Finance Yet

Tech vs. People. Where Should Finance Invest?

A Digital Reality Check Of The Finance Function

How To Make Robots A Part Of The Finance Family?

Why You Should Only Robotize Standard Processes 

Robots and Humans. A Marriage Made In Heaven Or Hell?

A Tale Of Robots: From Assembly Lines To Knowledge Workers

Robots Must Solve Business Pains To Be Successful

What AI Competencies Do Your Finance Team Really Need?

Here's How To Test If Your AI Solution Will Be A Success

You're The User Of AI. Yes You, So Take Charge!

Blip. Blop. Accounting Robot. Are You Ready?

Are You Ready For Robotics Process Automation?

Have You Met Your Robot Accountant Yet?

Robots Are The Future Of Analytics

Your Robot Accountant Has A Name, It's Dixie

Anders Liu-Lindberg is the co-founder, COO (Chief Operating Officer), and CMO (Chief Marketing Officer) at the Business Partnering Institute and owner of the largest group dedicated to Finance Business Partnering on LinkedIn with more 8,000 members. I have ten years of experience as a business partner at the global transport and logistics company Maersk. I am the co-author of the book “Create Value as a Finance Business Partner” and a long-time Finance Blogger with 40.000+ followers.