How to upgrade from VLOOKUPs to XLOOKUPs for your FP&A role

Hey there,

Today’s post is a technical one: replacing VLOOKUP formulas with XLOOKUP.

You’ll learn the following:

• What VLOOKUP and XLOOKUP functions are and their differences

• Why these functions are important for your FP&A role

• Example using XLOOKUP with step-by-step instructions

• Use cases using XLOOKUP functions

The cool thing about this formula is you can use it on both Excel and Google Sheets.

Now let’s dive in.

💭Differences between VLOOKUP vs. XLOOKUP

If you’ve worked in spreadsheets, chances are you’ve used the VLOOKUP formula at some point.

But if you haven’t, no worries. We got you covered. 😎

So what exactly are VLOOKUP and XLOOKUP formulas?

In very simple terms, they help you combine and find data in other tables.

For example, let’s say you have two data sets: sales orders and customers.

You can use the VLOOKUP formula to combine the customer table with the sales order data.

By doing so, you’ll know exactly which customers those sales are tied to.

VLOOKUP Limitations

While VLOOKUPS are amazing (trust us, we’ve used them for +20 years), they have their limitations.

They include:

• Limited to vertical lookup only (can’t go left to right)

• Requires data to be in ascending order

• Can only return the first matching value

• Data tag used to join your other table MUST be on the left-most column

Might not sound painful…yet.

But when dealing with large amounts of data, VLOOKUP can become a headache.

XLOOKUP and why they’re different

XLOOKUPs are a new formula introduced in Excel 2021 and 365. (You can also use them in Google Sheets.)

Simply put, XLOOKUP replaces the previous HLOOKUP, VLOOKUP, and LOOKUP functions by combining them into one.

It also replaces the INDEX+MATCH combo. We love them, but XLOOKUP is so much easier to use.

How cool is that? 😎

Overall, XLOOKUPs are more powerful and versatile than VLOOKUPs.

Here are some benefits of using XLOOKUPs over VLOOKUPs and why we prefer them:

• Look up data horizontally as well as vertically

• Perform exact and approximate matches, providing you with flexibility in your FP&A analysis

• Return multiple values from a table, making it easier to analyze complex data

• Data used to join your other table does NOT have to be set to the left-most column (lifesaver for those VLOOKUP oldtimers)

That last bullet alone is worth using this function. 👍

📎Why LOOKUP functions are important for your job

[Want to jump right to the use case? Jump 👇)

In FP&A, we love data. Tons of them.

Not kidding.

Whether you’re transitioning from Accounting, a current Analyst, or the CFO of a venture-backed company, chances are you’re going to dig through tons of data.

Digging for data optimally requires the right tools at your disposal.

You’ll need a way to tie all this data together easily and seamlessly.

This is where the XLOOKUP functions come in handy.

Depending on your level and specific job function, business partners throughout your org are going to reach out with specific asks.

Maybe it’s Marketing. Perhaps Operations.

Whoever it is, you’ll be asked to scrape tons of data, analyze them, and provide suggestions and recommendations.

Leverage the XLOOKUP formula.

💻Example with step-by-step instructions

Now that we understand what XLOOKUP functions are and why they’re a better alternative to VLOOKUP, let’s test it out.

Example Scenario

• Company name: Awesome, Inc.

• Objectives:

  • analyze sales order data

  • combine customer data with sales order data

  • list our top/bottom customers by sales total

• Goal: share top/bottom customers with our Sales business partner friends

For simplicity, we’ll assume we have already exported our data.

Let’s dig further.

Step 1: Create a data table for both data sets

Let’s create data tables for both our sales order and customer data sets.

Refer here for how to create data tables easily.

Refer here for how to create data table formulas.

Data tables will allow us to easily track our data, make it clean, and ensure our formulas always drag down.

👉 Data table shortcut = CTRL + T

Step 2: Create new columns labeled “Customer Name” and “Weeknum”

Let’s now create 2 new columns to pull in our customer name data and weekly data.

Add a new column labeled “Customer Name” as shown below:

Similarly, add a column called “Weeknum” using the formula below:

Formula = weeknum(OrderData_field)

We’ll use this to see how sales have trended week over week for the current month.

You should see this:

Now it’s time to use our handy XLOOKUP formula to pull in our customer name data.

Step 3: Create your XLOOKUP formula

In any cell (this is why data tables rock 😉), enter the following formula:

Customer name formula = XLOOKUP (column with your data tag, column with same data tag in your customer data table, column with the customer name you’re outputting)

You should see formula magic happening like below:

How slick was that? 🥰

🔥TIP: Don’t forget to name your data tables. Makes it super easy to remember which table is which.

Let’s keep the fun going!

Step 4: Create pivot tables to analyze the data

Now the fun part:

Analysis!

Create a pivot table using the shortcut: ALT + N + V + ENTER

Now take the following actions:

• Drag customer names under rows

• Drag weeknum under columns

• Drag Total Sales amount into values

You should see something like this:

Any insights? Findings?

In our example above, we’ve concluded:

• Bamazon was our top sales customer with = ~$4k

• Arples was our bottom sales customer with = ~$200

• Highest grossing sales week = week #4

Interesting….

Of course, this is a VERY simple use case. But you get the idea.

With XLOOKUPs, you can bring in all sorts of data such as:

• Operational data

• HR data

• Marketing data

• Supply Chain data

• Financial data

• Date data

Pick and choose wisely. 😉

💼Use Cases

Now that you’ve become an XLOOKUP master, feel free to use it within your current role and org.

Here are some ideas to leverage below:

1. Sales

   1. Closed/Open deals analysis

   2. QTD sales performance analysis

2. Marketing

   1. Customer lifetime value analysis

   2. Marketing spend analysis

3. Corporate Finance / Business Unit Finance

   1. Profit forecasting

   2. Attrition analysis

4. Operations Finance

   1. Cost to Serve analysis

With XLOOKUP formulas, start easily bringing in additional data for your analysis to drive business partner success and get that well-deserved promotion.

Who doesn’t want that? 🖐

Conclusion: XLOOKUPs are worth it!

Start implementing XLOOKUPs today.

They make your FP&A data analysis so much easier.

Those business partner requests that used to take hours now take mere minutes.

Seriously, we’ve helped our companies save thousands of hours using this simple formula and you can too.

Take the challenge of implementing this new formula in your role today.

Embed them into all your analyses.

Once you start using XLOOKUPs, there's no turning back. 💪

---

How do you feel now? Excited to implement XLOOKUP formulas into your FP&A analysis and requests?

Let us know by replying here or emailing us. If there are other topics you’d like us to cover as well, let us know!

Now go have fun making an impact on your business and your career!


Cheers,
Drew & Yarty

👇 Get our latest tips, tricks, and resources by subscribing below