How to create many-to-many lookup in Excel

In Excel, you can perform a many-to-many lookup to find multiple values based on a single criterion. This is useful for tasks such as finding all the books that a certain author has written for a specific publisher, or all the products that a certain customer has bought from a specific store.

We will show you how to perform a many-to-many lookup in Excel using different functions and formulas.

What is a Many-to-Many Lookup?

A many-to-many lookup is a type of lookup where you have more than one value in both the lookup column and the return column.

For example, in the table below, we have a list of authors, publishers, and books. Each author can write multiple books for multiple publishers, and each publisher can publish multiple books by multiple authors. This is a many-to-many relationship.

If we want to look up all the books that the author has written for the publisher, we cannot use a simple VLOOKUP or INDEX-MATCH formula, because they can only return one value per lookup.

We need a formula that can return multiple values for a single lookup value. This is where a many-to-many lookup comes in handy.

How to Perform a Many-to-Many Lookup in Excel?

There are different ways to perform a many-to-many lookup in Excel, but one of the most common and versatile methods is to use a combination of SUMPRODUCT, INDEX, SMALL, IF, ISNUMBER, ROW, and ROWS functions.

See also  How to Add Special Characters in Excel

Prepare the data

The first step is to prepare your data in a table format, with headers and no blank rows or columns. Make sure that your lookup column and return column have unique values, or at least no duplicates within the same row.

many to many lookup data table

Count the Number of Matches

The next step is to count how many matches there are for your lookup value. Lay out what you are looking for; in this case, I am looking for Timothy Garnish, who is also TG.

many to many lookup looking value sumproduct
To do this, we can use the SUMPRODUCT function, which multiplies corresponding elements in two or more arrays and returns the sum of the products.

Type in this formula =SUMPRODUCT((B4:B9=B11)*(A4:A9=B12)*(C4:C9))

sumproduct formula

We just looked up the sum of all books that Timothy Garnish has written for the Thimpson Publishing House, as it is showing above.

How to return many items for a single lookup value?

There are different ways to return many items for a single lookup value in Excel.

We are going to use COUNT function in one single formula to find many items.

single formula data table

Type in =COUNT(A4:A10) in the formula box.

return many items count formula

The lookup formula

Type =IF(ROWS(C$2:C2)<=$B$2,INDEX($A$2:$A$10,SMALL(IF(ISNUMBER($A$2:$A$10),ROW($A$2:$A$10)-ROW($A$2)+1), ROWS(C$2:C2))),””) and then press F2.

Now hold CTRL + SHIFT as it is an array function.

empty cell long formula

By following the steps in this tutorial, you can learn how to perform a many-to-many lookup in Excel. This can be a useful way to find multiple values based on a single criterion.