How to Lookup with Multiple Criteria in Excel (Both AND or OR Type)

How to Lookup with Multiple Criteria in Excel (Both AND or OR Type)

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 look up one or more values that satisfy multiple criteria in a data set in Excel.


Download Practice Workbook


How 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.

A Data Set in Excel

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


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 out the employee with an ID greater than 400 and a salary greater than $40000.

You can accomplish the task in 3 different ways.


1. Using INDEX-MATCH Function

Before going to the main point, you may go and take a glance at the INDEX and MATCH functions of Excel.

The INDEX-MATCH formula to find out the employee with an ID greater than 400 and a salary greater than $40000 will be:

=INDEX(C4:C20,MATCH(1,(B4:B20>400)*(E4:E20>40000),0),1)
[This is an Array Formula. So do not forget to press Ctrl + Shift + Enter unless you are in Office 365.]

INDEX-MATCH Formula to Lookup Multiple Criteria of AND Type

 

Look, we have found an employee with an ID greater than 400 and a salary greater than $40000, Richard Samuelson.

Explanation of the Formula

  • B4:B20>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, FALSE otherwise.

Formula to Find ID Greater than 400 or Not

  • E4:E20>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, FALSE otherwise.

Formula to Find Salary Greater than $40000 or Not

  • (B4:B20>400)*(E4:E20>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.

Returns 0 otherwise.

Two Arrays Multiplied Together

  • MATCH(1,(B4:B20>400)*(E4:E20>40000),0) goes through the array (B4:B20>400)*(E4:E20>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 (See the picture above)

MATCH Formula with Multiple Criteria

  • Finally, INDEX(C4:C20,MATCH(1,(B4:B20>400)*(E4:E20>40000),0),1) returns the Employee name from the range C4:C20, with row number equal to the output of the MATCH function and column number equal to 1.

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.

Yes. You are right. The formula will be:

=INDEX(C4:C20,MATCH(1,(D4:D20<DATE(2009,12,31))*(E4:E20<25000),0),1)

INDEX-MATCH Formula to Lookup Multiple AND Criteria

See, it is Angela Hopkins.


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.

The XLOOKUP formula to find out the employee with an ID greater than 400 and a salary greater than $40,000 will be:

=XLOOKUP(1,(B4:B20>400)*(E4:E20>40000),C4:C20)

XLOOKUP Formula to Lookup Multiple Criteria

See, we have got the same employee as earlier, Richard Samuelson.

Explanation of the Formula

  • (B4:B20>400)*(E4:E20>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 previous section).
  • XLOOKUP(1,(B4:B20>400)*(E4:E20>40000),C4:C20) first searches for 1 in the array (B4:B20>400)*(E4:E20>40000). When it finds one, it returns the value from its adjacent cell in the range C4:C20.

This is the name of the employee with an ID greater than 400 and a salary greater than $40,000.


3. Using FILTER Function

The INDEX-MATCH and the XLOOKUP formula have one limitation. If more than one value satisfies the given criteria, they return only the first value.

For example, in the earlier example, if you look closely, you will find that there are two employees with an ID greater than 400 and salary greater than $40,000.

Richard Samuelson and Usman Malik.

But the INDEX-MATCH and the XLOOKUP formula return only the first employee, Richard Samuelson.

To get all the values that satisfy a given criteria, you can use the FILTER function of Excel.

Before going to the main point, you may have a glance at the FILTER function of Excel.

But remember, the FILTER function is also only available in Office 365.

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

=FILTER(C4:C20,(B4:B20>400)*(E4:E20>40000))

FILTER Formula to Lookup Multiple Criteria

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

Explanation of the Formula

  • (B4:B20>400)*(E4:E20>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(C4:C20,(B4:B20>400)*(E4:E20>40000)) goes through all the values in the array (B4:B20>400)*(E4:E20>40000), and when it finds a 1, it returns the adjacent value from the range C4:C20.
  • 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 1 Jan, 2014 and 31 Dec, 2016, but receive a salary of at least $30,000.

Yes. You are right. The formula will be:

=FILTER(C4:C20,(D4:D20>=DATE(2014,1,1))*(D4:D20<=DATE(2016,12,31))*(E4:E20>=30000))

FILTER Formula to Lookup AND Type Multiple Criteria


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 means 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.


1. Using INDEX-MATCH Function

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

The INDEX-MATCH formula will be:

=INDEX(C4:C20,MATCH(TRUE,((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0,0),1)
[Array Formula. So do not forget to press Ctrl + Shift + Enter unless you are in Office 365.]

INDEX-MATCH Formula to Lookup OR type Multiple Criteria

See, we have got Jack Simpson, the first employee with a Joining Date before 1 Jan, 2010, or 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 using the FILTER function later.

Explanation of the Formula

  • D4:D20<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.

Joining Date before 2010 or Not

  • E4:E20>30000 also returns an array of TRUE and FALSE. TRUE when the salary is greater than $30,000. FALSE otherwise.

Salary Greater than $30,000 or Not

  • (D4:D20<DATE(2010,1,1))+(E4:E20>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.

Two Arrays Added Together

  • ((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0 goes through all the values of the array (D4:D20<DATE(2010,1,1))+(E4:E20>30000) and returns TRUE if the value is greater than 0 (1 and 2), and FALSE otherwise (0).

Two Criteria Added and Compared

  • MATCH(TRUE,((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0,0) goes through all the values in the array ((D4:D20<DATE(2010,1,1))+(E4:E20>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. See the above picture.

MATCH Formula for OR Type Multiple Criteria

  • Finally INDEX(C4:C20,MATCH(TRUE,((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0,0),1) returns the employee name from the range C4:C20 with the serial number returned by the MATCH function.

This is the required employee who matches at least one criterion.

Now, if you understand this, can you tell me the formula to find out the employee with ID less than 300 or Joining Date less than 1 Jan, 2012 or salary greater than $30,000?

Yes. You are right. The formula will be:

=INDEX(C4:C20,MATCH(TRUE,((B4:B20<200)+(D4:D20<DATE(2012,1,1))+(E4:E20>30000))>0,0),1)

INDEX-MATCH Multiple Criteria OR Type


2. Using XLOOKUP Function

You can accomplish the same task using the XLOOKUP function of Excel.

Visit this link to know about the XLOOKUP function before proceeding, if you wish.

(XLOOKUP is only available in Office 365)

The formula to find out the employee with joining date before 1 Jan, 2010 or salary greater than $30,000 will be:

=XLOOKUP(TRUE,((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0,C4:C20)

XLOOKUP Formula to Lookup Multiple Criteria of OR Type

See, we have got the same employee as earlier, Jack Simpson.

But as with the INDEX-MATCH formula, more employees meetlookup the given criteria. We have got only the first one.

Explanation of the Formula

  • ((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0 returns TRUE when at least one of the two criteria is satisfied, otherwise FALSE. See the above section.
  • XLOOKUP(TRUE,((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0,C4:C20) then returns the employee name from column C4:C20, where it gets the first TRUE.

3. Using FILTER Function

Finally, we will accomplish the same task using the FILTER function of Excel.

Click here to have a glance at the FILTER function before proceeding, if you wish.

(FILTER is only available in Office 365)

This time we will get all the employees who joined before 1 Jan, 2010 or receive salaries greater than $30,000.

The formula will be:

=FILTER(C4:C20,((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0)

FILTER Function to Lookup Multiple Criteria of OR Type

See, this time we have got all the employees who meet our given criteria, joining date before 1 Jan, 2010 or salary greater than $30,000.

Explanation of the Formula

  • ((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0 returns TRUE when at least one of the two criteria is satisfied, otherwise FALSE. See the INDEX-MATCH section.
  • FILTER(C4:C20,((D4:D20<DATE(2010,1,1))+(E4:E20>30000))>0) goes through all the cells in the range C4:C20 but returns only those when it encounters a TRUE.
  • Thus it returns all the employees who meet at least one among the given criteria.

Conclusion

Using these methods, you can look up 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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

1 Comment
  1. 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.

Leave a reply

ExcelDemy
Logo