The MULTILOOKUP function is used to look up values based on multiple criteria. MULTILOOKUP works like an XLOOKUP or VLOOKUP on multiple conditions.

What is the MULTILOOKUP formula?

The MULTILOOKUP formula is =MULTILOOKUP(return_range, key_range1, key1, [key_range2], [key2], ...).

The XLOOKUP function is used to search for a value in a lookup range and return a corresponding value from a return range. You can use XLOOKUP formulas to perform exact and approximate matches, search in reverse, and handle errors. It offers enhanced capabilities compared to traditional lookup functions XLOOKUP is a modern and more powerful replacement for VLOOKUP.

Can you VLOOKUP multiple criteria?

VLOOKUP is not built to support multiple criteria easily. MULTILOOKUP is better for multiple criteria and in general XLOOKUP is better than VLOOKUP. It's easier to do an XLOOKUP with multiple critieria than a VLOOKUP on 2 criteria or more.

Why are lookups in Excel slow sometimes?

Lookup functions are memory intensive. When running a lookup over large data sets in Excel or Google sheets, the spreadsheet can slow down or crash. Lookups generally search every row in the data set looking for a match. As the data set grows, there are more pieces of data to look through, thus increasing the memory requirement on the computer.

What is the best way to lookup multiple criteria in Excel and Google Sheets?

In place of MULTILOOKUP, you can XLOOKUP multiple criteria in Excel and Google Sheets using the following syntax: =XLOOKUP(1, (criteria1_range = criteria1_value) * (criteria2_range = criteria2_value), return_range).

How to use MULTILOOKUP to lookup with multiple criteria

The MULTILOOKUP function makes it easy to look up values based on multiple criteria in a spreadsheet. MULTILOOKUP works like XLOOKUP and VLOOKUP but allows for multiple conditions. MULTILOOKUP is a unique function to Row Zero, a next-gen spreadsheet designed to work with large datasets and complex calculations. Continue reading to learn how to use MULTILOOKUP, as well as XLOOKUP with multiple criteria.

View live MULTILOOKUP examples

What is MULTILOOKUP?
How to use MULTILOOKUP
MULTILOOKUP examples
XLOOKUP using multiple criteria
Common MULTILOOKUP mistakes to avoid

What is MULTILOOKUP?

MULTILOOKUP is a spreadsheet function that does a multiple criteria lookup. MULTILOOKUP works like XLOOKUP and VLOOKUP but lets you lookup based on multiple conditions.
example MULTILOOKUP that uses multiple criteria to lookup a value The MULTILOOKUP function uses the following syntax:

=MULTILOOKUP(return_range, key_range1, key1, [key_range2], [key2], ...)

The first 3 arugments are required:

return_range is the range of cells containing the corresponding values you want to retrieve.
key_range is the range of cells where you want to perform the lookup.
key is the key value you want to find.

And then you can optionally add as many key_range and key pairs as you'd like to lookup across multiple criteria.

How to use MULTILOOKUP

Here is a MULTILOOKUP tutorial that shows how to use MULTILOOKUP in a spreadsheet:

MULTILOOKUP Examples

Below, you'll find sample MULTILOOKUP examples that demonstrate how to do a MULTILOOKUP. You can view these MULTILOOKUP examples live in a spreadsheet here.

MULTILOOKUP using 2 criteria

Here is a basic lookup using two criteria: example multi lookup using 2 criteria example

MULTILOOKUP using 4 criteria

Here is an example of MULTILOOKUP using many criteria: multilookup with many criteria As you can see, it's easy to use lookup using many criteria.

MULTILOOKUP greater than / less than using a helper column

Spreadsheet lookup functions don't allow you to evaluate operators like greater than, less than, etc. To lookup greater than, for example, you can create a helper column using an IF formula. Your helper column can evaluate using an operator (e.g. >= 1000) to create an easy lookup value to incorporate into your MULTILOOKUP formula.

Here's an example of MULTILOOKUP greater than: multilookup greater than using IF and a helper column

In this instance, the formula for the helper column looks like this: IF(C34>=1000, 1, "") which evaluates to 1 if the delivery amount is greater or equal to 1000. Using a lookup function and IF lets you evaluate a a much broader range of lookup criteria.

XLOOKUP with multiple criteria

If you prefer sticking with XLOOKUP, it is possible to lookup multiple values with XLOOKUP. Also, if you want to lookup multiple criteria in Excel or Google Sheets, you'll need to use this trick, since neither of them support the MULTILOOKUP function.

Here's how to XLOOKUP multiple criteria using expressions: simple xlookup example When you do an XLOOKUP with multiple criteria, it looks for the first instance where all criteria match and returns the corresponding value in the return range.

The syntax for XLOOKUP with multiple criteria is: XLOOKUP(1, (criteria1_range = criteria1_value) * (criteria2_range = criteria2_value), return_range)

Here's how it works:

(criteria1_range = criteria1_value) creates a Boolean array (TRUE/FALSE).
Multiplying multiple conditions (*) acts like an AND condition, converting TRUE/FALSE into 1/0.
The lookup value is 1 because the combined condition will return 1 where all conditions are TRUE.
return_range is the range of values to return when the criteria are met.

You can use more than two criteria if needed since this formula is effectively mutliplying 1 and/or 0 for each criteria. However, using many XLOOKUP criteria may impact performance in Excel and Google Sheets.

Common MULTILOOKUP Mistakes to Avoid

While MULTILOOKUP is easy to use, there are some common MULTILOOKUP errors to avoid:

Mismatched Ranges: Ensure key_range and return_range are the same size length. For example, you would get an error in this formula =MULTILOOKUP("A1:A100, B1:B101,"SFO", C1:101, "SEA") because the return range is smaller than the lookup ranges.
Duplicate values: By default, MULTILOOKUP only returns the first match it finds when all of lookup criteria is met. If you have several rows where all of your criteria matches with different corresponding values in the return range, you may not get the data you want. In this scenario, it can be helpful to sort your return range so it can return something logical like the biggest, smallest, earliest, or latest.
Heavy usage in a traditional spreadsheet - MULTILOOKUP is a unique function to Row Zero, which is designed to work with large datasets and complex calculations. If you try replicating with VLOOKUP and multiple criteria in Excel or Google Sheets, it can cause performance issues, especially if using many lookups or many criteria with a large dataset. If your lookups are slow in Excel, try using a more powerful spreadsheet like Row Zero.
Inconsistent data types or formatting - Confirm the data types and formats between the key and key_range are compatible. For example, if you have a ZIP code that is a number as your key value but it's text in your key range, the MULTILOOKUP will fail.

Conclusion

The MULTILOOKUP function is an easy way to lookup using multiple criteria in a spreadsheet. You can also use MULTILOOKUP to join multiple CSV files or datasets in a spreadsheet. MULTILOOKUP works like an XLOOKUP or VLOOKUP on multiple criteria. If you need MULTILOOKUP help, step through the MULTILOOKUP examples above. MULTILOOKUP is a unique function to Row Zero, which is designed to work with 1000x larger datasets than traditional spreadsheets and is a good alternative to Excel and Google Sheets for big data users. You can try Row Zero for free to explore the MULTILOOKUP function.

Try Row Zero for free