How to Find Data in Excel Using a Formula

Trying to find a specific piece of data within a sprawling Excel spreadsheet can feel like searching for a needle in a haystack. But instead of scanning rows and columns manually, you can use lookup formulas to have Excel fetch the information for you in an instant. This guide will walk you through the three core methods for finding data in Excel, from the classic VLOOKUP to the more powerful newcomers, INDEX/MATCH and XLOOKUP.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Do Lookup Formulas Do?

In short, a lookup formula finds a specific value in one column and returns a corresponding value from a different column in the same row. It’s the spreadsheet equivalent of saying, "Find the price for 'Product A'" or "What is Jane Doe's email address?"

Let's use a practical example that we'll come back to throughout this tutorial. Imagine you have two separate tables:

• A Sales Data table with Order IDs, Customer IDs, and Order Dates.
• A Customer Details table with Customer IDs, Customer Names, and their location.

Your goal is to pull the Customer Name from the Customer Details table and add it to a new column in your Sales Data table, matching it based on the Customer ID. This is a perfect job for a lookup formula.

The Classic Method: VLOOKUP

VLOOKUP is arguably the most well-known lookup formula in Excel. The "V" stands for vertical because it scans down the first column of a table to find your value.

It's a reliable workhorse for straightforward lookups, but as we'll see, it has some important limitations.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Breaking Down the VLOOKUP Formula

The syntax for VLOOKUP looks like this:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let's break that down piece by piece:

• lookup_value: This is the piece of data you already have. In our example, it's the Customer ID in the Sales Data table.
• table_array: This is the range of cells where the answer lives. For us, this is the entire Customer Details table. It's important to note that the column containing the lookup_value must be the first column in this array.
• col_index_num: This is the number of the column in your table_array that contains the value you want to get back. Since we want the Customer Name (the second column in our Customer Details table), we'd enter 2.
• [range_lookup]: This is an optional argument that tells Excel whether to find an exact match or an approximate match. You'll want to use FALSE almost 100% of the time to find an exact match. If you omit it or use TRUE, Excel might return an incorrect but "close" match, which is rarely what you want.

VLOOKUP in Action

Let’s go back to our example. Our Sales Data is in columns A & B, and our Customer Details table is in columns E, F, and G.

Worksheet View:

• Column A (Sales Data): Order ID
• Column B (Sales Data): Customer ID
• ...
• Column E (Customer Details): Customer ID
• Column F (Customer Details): Customer Name
• Column G (Customer Details): Location

To pull the customer's name into column C next to our sales data, we'd click into cell C2 and type the following formula:

=VLOOKUP(B2, E:G, 2, FALSE)

• B2: We're looking for the customer ID located in cell B2.
• E:G: We're searching within the customer details table, which spans columns E through G. (Using $E$2:$G$100 is also common practice to lock the range if you're an absolute references fan).
• 2: We want the value from the 2nd column of that table (Column F).
• FALSE: We demand an exact match for the customer ID.

After hitting enter, Excel grabs the value in B2, finds it in column E, and returns the corresponding name from column F. You can then drag this formula down the rest of your column C to populate all the customer names.

The Limitations of VLOOKUP

While useful, VLOOKUP has a few downsides:

• It only looks right: VLOOKUP can only search for a value in the first column of a table and return a value from a column to its right. It can't look left.
• It's fragile: The col_index_num is a hardcoded number. If you insert or delete a column in your data source table, the formula breaks because the column number is now incorrect.
• It can be slow: On very large datasets, VLOOKUP can be inefficient because it often loads more data into memory than necessary.

The Pro Combo: INDEX and MATCH

For decades, the answer to VLOOKUP's limitations was INDEX and MATCH. Using these two functions together creates a more flexible and powerful lookup that overcomes all of VLOOKUP's shortcomings.

Understanding the Two Parts Separately

Instead of one function that does everything, this method uses two specialized functions that work in tandem.

1. The MATCH Function

The MATCH function's job is simple: it tells you the position of an item in a list. It doesn't return the item itself, just its row or column number.

Its syntax is:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value you're looking for (e.g., our Customer ID in cell B2).
• lookup_array: The single row or column where you're looking for it (e.g., column E, where all Customer IDs are).
• [match_type]: Use 0 for an exact match.

So, a formula like =MATCH(B2, E:E, 0) would return a number - the row number where that specific customer ID is located.

2. The INDEX Function

The INDEX function's job is equally simple: it returns the value of a cell from a specific row and column number within a range you define.

Its syntax is:

=INDEX(array, row_num, [column_num])

• array: The range of cells you want to pull a value from (e.g., column F, where all the Customer Names are).
• row_num: The row number you want a value from.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Combining INDEX and MATCH

Now, let's put them together. We use MATCH to find the row number of our customer, and then we feed that number into the row_num argument of the INDEX function to get the name from that row.

The combined formula looks like this:

=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))

Using our example one more time, the formula in cell C2 would be:

=INDEX(F:F, MATCH(B2, E:E, 0))

• The MATCH(B2, E:E, 0) part runs first. It finds the Customer ID from B2 somewhere in column E and returns its row number. For example, it might find it on row 42.
• The formula then becomes =INDEX(F:F, 42). Excel goes to column F (the Customer Names), finds the 42nd row, and returns the name found in that cell.

This combo is more robust than VLOOKUP because it's not affected by adding or deleting columns. Since it references F:F and E:E directly, you can move those columns around, and the formula will still work.

The New Champion: XLOOKUP

Available for Microsoft 365 users and in Excel 2021 and later, XLOOKUP is the modern successor to both VLOOKUP and INDEX/MATCH. It combines the simplicity of VLOOKUP with the power of INDEX/MATCH, and then adds a few brand new perks.

Breaking Down the XLOOKUP Formula

XLOOKUP streamlines the process significantly. The basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Look how much cleaner that is:

• lookup_value: Still the value you have (our Customer ID in B2).
• lookup_array: The single column containing the IDs you're scanning (column E).
• return_array: The single column containing the names you want back (column F). No more column counting!
• [if_not_found]: An optional but amazing argument where you can specify what to show if no match is found (e.g., "Not Found"). This replaces complicated IFERROR formulas you'd normally have to wrap around a VLOOKUP.

It also defaults to an exact match, so you don't even have to specify FALSE or 0 anymore.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

XLOOKUP in Action

With XLOOKUP, our formula in cell C2 becomes beautifully simple:

=XLOOKUP(B2, E:E, F:F)

That’s it. You tell it what to look for, where to look, and what column to return. And if you want cleaner error handling, you can easily add your custom text:

=XLOOKUP(B2, E:E, F:F, "Customer not found")

Now, if an order has a Customer ID that doesn't exist in your customer table, you'll see a clean message instead of an ugly #N/A error.

Which Formula Should You Use?

So, with three great options, which one is right for your situation?

• Use VLOOKUP when... You need to maintain compatibility with very old versions of Excel, or for a very simple, quick-and-dirty lookup where your data is perfectly structured and won't be changing.
• Use INDEX/MATCH when... You are working in a version of Excel prior to Excel 365/2021. It's the most flexible and robust option for legacy versions and remains the gold standard for anyone who isn't on the latest software.
• Use XLOOKUP when... you can! If you and your team are using a modern version of Excel, XLOOKUP should be your default choice. It's easier to write, harder to break, offers built-in error handling, and is more readable than its predecessors.

Final Thoughts

Mastering VLOOKUP, INDEX/MATCH, and XLOOKUP will save you countless hours of manually searching through spreadsheets. Each has its place, but as Excel evolves, moving toward modern tools like XLOOKUP will make your data analysis process faster, cleaner, and less prone to errors.

Of course, spending your day structuring data and writing formulas in a spreadsheet is a common but slow way to get answers. We built Graphed because we believe your time is better spent on strategy, not spreadsheet wrangling. Instead of pulling data into sheets and joining it with formulas, you can connect your data sources to Graphed and simply ask questions in plain English - like "show me sales by customer name for last quarter" - and get an instant dashboard, freeing you up to act on insights instead of hunting for them.