This article explains how to create a lookup formula that uses multiple criteria in Excel to find information in a database or table of data using an array formula. The array formula involves nesting the MATCH function within the INDEX function. Information covers Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.
Using Excel VLOOKUP function with multiple criteria (multiple cells)
To follow the steps in this tutorial, enter the sample data into the following cells, as shown in the image below. Rows 3 and 4 are left blank to accommodate the array formula created during this tutorial. (Note: This tutorial does not include the formatting shown in the image.)
The INDEX function is one of the few functions in Excel that has multiple forms. The function has an Array Form and a Reference Form. The Array Form returns the data from a database or table of data. The Reference Form provides the cell reference or location of the data in the table.
In this tutorial, the Array Form is used to find the name of the supplier for titanium widgets, instead of the cell reference to this supplier in the database.
