Lookup with multiple criteria

If we have to look up items in tables where we can’t use unique identifiers (there are no names located in a table column that contains data in all of the rows, and that data is non-repeating), we will probably have to resort to matching multiple criteria in multiple columns.

 

Consider the following example:

table with no unique identifiers

Each year, multiple game tournaments are held in repeating cities, organized by various repeating organizers. Items in the column City are not unique, nor are they in the column Date or the column Organizer.

 

How can we retrieve the organizer email address for the tournament held in Prague in 2022?

We will have to match both the city of Prague in the column City, and the year 2022 in the column Date.

We will have to do this using array formulas.

 

We can look up multiple criteria using the INDEX MATCH combination in the following way:

lookup with multiple criteria INDEX MATCH combination

 

What is going on here?

In our first array nested inside the MATCH function, we are checking whether the city in column A equals Prague.

In our second array nested inside the MATCH function, after retrieving the year using the YEAR function, we are checking whether the year in column B equals 2022.

When we multiply those arrays, as TRUE is the same as 1 and FALSE is the same as 0, we will generate an array in which only items for which both Prague in the column A and the year 2022 in the column B will contain 1.

By looking up number 1, we will specify to our INDEX function to return the 5th row in the INDEX array.

lookup with multiple criteria INDEX MATCH combination process

 

We can look up multiple criteria using the XLOOKUP function in the following way:

lookup with multiple criteria XLOOKUP function

The logic remains the same, only the syntax is slightly different.

 

We can look up multiple criteria using the FILTER function in the following way:

lookup with multiple criteria FILTER function

The logic remains mostly the same, only the syntax is slightly different.

 

What would happen if there were two or more matches, i.e., two or more tournaments organized in the same city in the same year?

Both the INDEX MATCH combination and the XLOOKUP function look only for the first available match on the list, and only the first available match would be returned.

The FILTER function by default spills all available results; 2nd, 3rd, etc. matches would be spilled to the cells towards the bottom.

See also: Lookup the 2nd, the 3rd, or the nth value.

 

Dig deeper:

Lookup with unique identifiers

Combining SUMIFS with INDEX MATCH

XLOOKUP function

Combining SUMIFS with XLOOKUP

FILTER function

5 thoughts on “Lookup with multiple criteria”

Leave a Reply