While working in Excel, we are to do this often. We are to look up a particular value in a data set that satisfies one or more criteria. Today I will be showing how you can lookup one or more values that satisfy multiple criteria in a data set in Excel.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 2 Suitable Ways to Lookup with Multiple Criteria in Excel

Look at the data set below. We have the **Employee IDs, Employee Names, Joining Dates, **and **Salaries** of a company named **Jupyter Group**. We will lookup values with multiple criteria using the **INDEX, MATCH, XLOOKUP, **and **FILTER functions**. Here’s an overview of the dataset for our today’s task.

Now we will try to lookup values that meet various types of multiple criteria from this set of data.

### Method 1: Lookup Multiple Criteria of AND Type

First of all, let’s try to look up some multiple criteria of **AND **type. Here, **AND **type multiple criteria means, one value has to satisfy all the criteria to be selected. Let’s try to find an employee with an **ID **greater than **400 **and a salary greater than **$40000**. You can accomplish the task in **3** different ways.

#### 1.1 Combine INDEX and MATCH Functions in Rows and Columns

Before going to the main point, you may go and take a glance at the **INDEX** and** MATCH** functions of Excel. We will find out the employee with an **ID **greater than **400 **and a salary greater than **$40000** using the **INDEX-MATCH** formula. Let’s follow the instructions below:

**Steps:**

- First of all, select cell
**G7**and write down the following formula.

`=INDEX(C5:C16,MATCH(1,(B5:B16>400)*(E5:E16>40000),0),1)`

- After that, press
**Enter**on your keyboard. As a result, we have found an employee with an**ID**greater than**400**and a salary greater than**$40000**,**Richard Samuelson**.

**Formula Breakdown****B5:B16>400**goes through all the**IDs**in column**B**and returns an array of**TRUE**and**FALSE**,**TRUE**when an**ID**is greater than**400**, otherwise**FALSE**.**E5:E16>40000**goes through all the**salaries**in column**E**and returns an array of**TRUE**and**FALSE**,**TRUE**when a salary is greater than**$40,000**, otherwise**FALSE.****(B5:B16>400)*(E5:E16>40000)**multiplies the two arrays of**TRUE**and**FALSE**, and returns a**1**when the**ID**is greater than**400**and the salary is greater than**$40,000**. Otherwise returns**0**.**MATCH(1,(B5:B16>400)*(E5:E16>40000),0)**goes through the array**(B5:B16>400)*(E5:E16>40000)**and returns the serial number of the first**1**it encounters.- In this case, it returns 5 because the first 1 is in serial number 5.
- Finally,
**INDEX(C5:C16,MATCH(1,(B5:B16>400)*(E5:E16>40000),0),1)**returns the Employee name from the range**C5:C16**, with row number equal to the output of the**MATCH**function and column number equal to**1**.

**Notes**

**Array Formula**. So do not forget to press

**Ctrl + Shift + Enter**unless you are in

**Office 365**.

- This is the required employee with an
**ID**greater than**400**and a salary greater than**$40,000**. Now, if you understand this, can you tell me the formula to find out the employee who joined**before 31 Dec, 2009**, but still receives a salary**less than $25,000**. - Afterward, type the below formula in cell
**G7**.

`=INDEX(C5:C16,MATCH(1,(D5:D16<DATE(2009,12,31))*(E5:E16<25000),0),1)`

- Hence, hit
**Enter**. Further, you will get**Angela Hopkins**as the return of the formula.

**Read More:** **7 Types of Lookup You Can Use in Excel**

#### 1.2 Using XLOOKUP Function

We can accomplish the previous task using the **XLOOKUP** function of Excel too. But remember, **XLOOKUP** is only available in **Office 365**. Before going to the main point, you can have a glance at the **XLOOKUP** function of Excel. Now, we find out the employee with an **ID **greater than **400 **and a salary greater than **$40,000** using the **XLOOKUP** function. Let’s follow the instructions below to learn!

**Steps: **

- Firstly, type the below formula in cell
**G7**.

`=XLOOKUP(1,(B5:B16>400)*(E5:E16>40000),C5:C16)`

- As a result, we have got the same employee as earlier,
**Richard Samuelson**. This is the name of the employee with an**ID**greater than**400**and a salary greater than**$40,000**.

**Formula Breakdown****(B5:B16>400)*(E5:E16>40000)**returns an array of**1**and**0**,**1**when the**ID**is greater than**400**and the salary is greater than**$40,000**.**0**otherwise.**XLOOKUP(1,(B5:B16>400)*(E5:E16>40000),C5:C16)**first searches for 1 in the array**(B5:B16>400)*(E5:E16>40000).**When it finds one, it returns the value from its adjacent cell in the range**C5:C16**.

**Read More:** **How to Use LOOKUP Function in Excel (4 Suitable Examples)**

#### 1.3 Applying FILTER Function

The **INDEX-MATCH** and the** XLOOKUP** formula have one limitation. If more than one value meets the given criteria, they return only the first value. For instance, in the earlier example, if you look closely, you will find that there are **two **employees with an **ID **greater than **400 **and a salary greater than **$40,000**.They are **Richard Samuelson **and **Usman Malik. **But the **INDEX-MATCH** and the **XLOOKUP** formulas return **only the first** employee, **Richard Samuelson**. To get all the values that satisfy the given criteria, you can use the **FILTER** function of Excel. But remember, the **FILTER** function is also only available in **Office 365**.

**Steps:**

- To find out the employees with an
**ID**greater than**400**and a salary greater than**$40,000**the**FILTER**formula will be:

`=FILTER(C5:C16,(B5:B16>400)*(E5:E16>40000))`

- After that, this time we have got all the employees that maintain all the criteria,
**Richard Samuelson**and**Usman Malik**.

**Formula Breakdown****(B5:B16>400)*(E5:E16>40000)**returns an array of**1**and**0**,**1**when the ID is greater than 400 and the salary is greater than $40,000.**0**otherwise (See the**INDEX-MATCH**section).**FILTER(C5:C16,(B5:B16>400)*(E5:E16>40000))**goes through all the values in the array**(B5:B16>400)*(E5:E16>40000),**and when it finds a**1**, it returns the adjacent value from the range**C5:C16**.- Thus we get all the employees with an
**ID**greater than**400**and a salary greater than**$40,000**.

- Now, if you understand this, can you tell me the formula to find out the employees who joined between
**January 1, 2014,**and**December 31, 2016**, but received a salary of**at least $30,000**? Yes. You are right. The formula will be:

`=FILTER(C5:C16,(D5:D16>=DATE(2014,1,1))*(D5:D16<=DATE(2016,12,31))*(E5:E16>=30000))`

**Read More:** **How to Lookup Multiple Values in Excel (10 Ways)**

### Method 2: Lookup Multiple Criteria of OR Type

Now, we will try to look up some values that satisfy multiple criteria of **OR** type. Here, **OR** type criteria mean one value has to satisfy at least one criterion among all the criteria to be selected. Let’s try to find out the employee who joined before **1 Jan, 2010 **or receives a salary greater than **$30,000**.

#### 2.1 Merge INDEX and MATCH Functions in Date Range

Click here to visit the **INDEX **function and click here to visit the **MATCH** function before proceeding, if you wish.

**Steps:**

- The
**INDEX-MATCH**formula will be as shown in below formula box.

`=INDEX(C5:C16,MATCH(TRUE,((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0,0),1)`

- See, we have got
**Jack Simpson**, the first employee with a joining date**before January 1, 2010**, or a salary**greater than $30,000**. But there are many more employees. Using**INDEX-MATCH,**we get only the first one. - We will get all the employees together later using the
**FILTER**function later. This is the required employee who matches at least one criterion.

**Formula Breakdown****D5:D16<DATE(2010,1,1)**returns an array of**TRUE**and**FALSE**.**TRUE**when the joining date in column**D**is less than 1 Jan 2010.**FALSE**otherwise.**E5:E16>30000**also returns an array of**TRUE**and**FALSE**.**TRUE**when the salary is greater than $30,000.**FALSE**otherwise.**(D5:D16<DATE(2010,1,1))+(E5:E16>30000)**adds the two arrays and returns another array of**0, 1,**or**2**.**0**when no criterion is satisfied,**1**when only one criterion is satisfied and**2**when both the criteria are satisfied.**((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0**goes through all the values of the array**(D5:D16<DATE(2010,1,1))+(E5:E16>30000)**and returns**TRUE**if the value is greater than**0**(**1**and**2**), and**FALSE**otherwise (**0**).**MATCH(TRUE,((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0,0)**goes through all the values in the array**((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0**and returns the first serial number where it gets a**TRUE**.- In this case, returns
**3**because the first**TRUE**is in serial**3**. - Finally,
**INDEX(C5:C16,MATCH(TRUE,((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0,0),1)**returns the employee name from the range**C5:C16**with the serial number returned by the**MATCH**function.

Now, if you understand this, can you tell me the formula to find out the employee with an **ID **less than **300, **or a joining date less than January **1, 2012,** or a salary greater than **$30,000**?

Yes. You are right. The formula will be:

`=INDEX(C5:C16,MATCH(TRUE,((B5:B16<200)+(D5:D16<DATE(2012,1,1))+(E5:E16>30000))>0,0),1)`

**Read More:** **How to Lookup Text in Excel (7 Suitable Methods)**

#### 2.2 Applying XLOOKUP Function

You can accomplish the same task using the **XLOOKUP** function in Excel. **XLOOKUP** is only available in **Office 365**.

**Steps:**

- The formula to find the employee with a joining date before January
**1, 2010,**or a salary greater than**$30,000**will be:

`=XLOOKUP(TRUE,((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0,C5:C16)`

- See, we have got the same employee as earlier,
**Jack Simpson**. But as with the**INDEX-MATCH**formula, more employees meet the given criteria. We have got only the first one.

**Formula Breakdown****((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0**returns**TRUE**when at least one of the two criteria is satisfied, otherwise**FALSE**. See the above section.**XLOOKUP(TRUE,((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0,C5:C16)**then returns the employee name from column**C5:C16**, where it gets the first**TRUE**.

**Read More:** **How to Lookup Value from Another Sheet in Excel (3 Easy Methods)**

#### 2.3 Using FILTER Function

Finally, we will accomplish the same task using the **FILTER** function in Excel. The **FILTER** function is only available in **Office 365**. This time we will get all the employees who joined before **January 1, 2010, **or received salaries greater than** $30,000**.

**Steps:**

- The formula will be the same as shown in the below formula box.

`=FILTER(C5:C16,((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0)`

- Thus it returns all the employees who meet at least one of the given criteria.
- See, this time we have got all the employees who meet our given criteria, joining date before
**January 1, 2010,**or salary greater than**$30,000**.

**Formula Breakdown****((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0**returns**TRUE**when at least one of the two criteria is satisfied, otherwise**FALSE**. See the**INDEX-MATCH**section.**FILTER(C5:C16,((D5:D16<DATE(2010,1,1))+(E5:E16>30000))>0)**goes through all the cells in the range**C5:C16**but returns only those when it encounters a**TRUE**.

**Read More:** **How to Lookup a Table in Excel (8 Methods)**

## Conclusion

Using these methods, you can lookup some value that satisfies multiple criteria from any set of data. Do you know any other method? Or do you have any questions? Feel free to ask us.

Can I use lookup to populate the cells of one spreadsheet with the information from another using two separate criteria(date and text)? I’ve tried using Vlookup but I keep having issues with the date. Google sheets keep misinterpreting it.

Hi Temitope,

Our website currently focuses on providing articles and solutions for Microsoft Excel only. If you have the same problem in Excel, please provide us with a little bit more information. We will try to assist you as much as possible.