How to Vlookup with Multiple Criteria in Different Excel Sheets

Typically, the VLOOKUP function in Excel looks up a single value based on a specific condition. But sometimes we may need to search using two or more criteria when the data may be stored in different sheets.

Though VLOOKUP can’t do it directly, there’s also a simple trick to fix this problem. We can simply apply different functions like INDEX- Match to vlookup with multiple criteria in different sheets.

This is one of the most common methods to vlookup with multiple criteria in Excel. The INDEX + MATCH combination gives us a more flexible and powerful way to do this without adding a helper column. Unlike VLOOKUP, which searches based on one condition and requires a helper column, INDEX + MATCH allows us to check multiple columns at the same time without rearranging data.

So, here in the given example, we have two different datasets named SalesData and Payment Data. SalesData includes Customer name, Product, Sales, Price and Date, while Payment Data has the Date and Payment Type.

SalesData Worksheet:

PaymentData Worksheet:

Now in a different worksheet, we’ll find out the Payment Method based on a specific customer name, product name, and a specific date he purchased the product. To do so, we’re going to apply the INDEX-MATCH combination. Let’s see how it works.

Steps:

➤ In the Lookup Sheet, enter the criteria as we have the name John Stalin and the product name Keyboard which he purchased on 1st January 2025.
➤ Now insert the following formula in cell B4:

➤ Press Enter and you’ll get the correct Payment Type John Stalin used as we can see in the image below.

XLOOKUP is incredibly powerful and can handle multiple criteria more accurately without array formulas like INDEX-MATCH. And this would be the easiest method to vlookup multiple criteria in different sheets. It prefers fewer arguments compared to the INDEX-MATCH combination. But this function is only available on Microsoft 365 and Excel 2021. It’s not compatible with older versions like Excel 2019 and prior.

So, if you’re using MS 365 or Excel 2021, we can also apply the XLOOKUP function as an alternative to vlookup multiple criteria across sheets in Excel. For example, the customer named Mary Fowler purchased three Mouse on 2nd January in 2025. Now we’ll have to find the payment method he used to pay his bill. And this data is stored in a different sheet. Using XLOOKUP, we can simply do so and here’s how it works.

Steps:

➤ To begin with, select your criteria in the lookup sheet (here Using XLOOKUP).
➤ Enter the formula in cell B4 as follows:

➤  Hit the Enter key and you’ll get your desired results as the image shows below.

Here, Mary Flower purchased Mouse on 2nd January in 2025. And how did she pay? With cash and very easily we’ve found it out through the XLOOKUP function with multiple criteria.

Another similar alternative to vlookup multiple criteria in different sheets in excel is using the FILTER function. This is quite simple as it uses direct syntax. This is also ideal for creating dynamic reports and dashboards.

Unlike XLOOKUP, the FILTER function can return multiple values matching the criteria. But it doesn’t have any built-in error handling like the XLOOKUP function has. However, let’s see how this method goes.

Steps:

➤ Once you selected your criteria, insert the formula as follows in cell B4:

➤ Now, tap Enter and the Payment Type for John Stalin will appear as follows.

Frequently Asked Questions (FAQs)

By Default, Can VLOOKUP Handle Multiple Criteria?

No, unfortunately it cannot. The VLOOKUP function can only look up based on a single criterion. To look up with multiple criteria we have to use alternative methods as we discussed earlier.

What is the easiest way to VLOOKUP with multiple criteria across different sheets?

Using the XLOOKUP function would be the easiest. It is the most efficient way since it supports multiple criteria without needing array formulas or helper columns.

What if my Excel version does not support XLOOKUP or FILTER?

If your Excel version is older and doesn’t support XLOOKUP or FILTER, you can use the INDEX + MATCH approach with helper columns or array formulas.

Can these methods handle large datasets efficiently?

Yes, INDEX-MATCH and XLOOKUP are efficient even for large datasets. However, the FILTER function and array formulas may slow down performance if the dataset is very large.

Concluding Words

That’s how we can simply vlookup with multiple criteria in different sheets in excel. Throughout the article, we have discussed all the possible methods you can choose from depending on your requirements.

These methods include everything from array formulas like combination of functions like INDEX + MATCH, and FILTER to the simple XLOOKUP function. If you want to apply an array formula or are using older versions of Excel, you better use the INDEX-MATCH combination. But if you prefer something very simple and have got access to Microsoft 365 or Excel 2021, XLOOKUP would be the best choice for you. So, hopefully now you can pick the best option for you and enjoy working in excel.