How to create many to many lookup in Excel

This tutorial article is about how to create a many-to-many lookup in Excel. 

Many to many lookup

It is something that begins with this data:

many to many lookup data table

Layout what you are looking for, in this case I am looking for Timothy Garnish (1), who is also TG (2).

many to many lookup looking value sumproduct

Click on an empty cell (1), and type in this following formula =SUMPRODUCT((B4:B9=B11)*(A4:A9=B12)*(C4:C9)) (2).

sumproduct formula

In conclusion, 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.

In this example, we are going to use different functions in one single formula to find many items.

But for the successfulness of this, the first thing you need is data that looks like this:

single formula data table

Note: You could see that there are multiple factors that shows in the data as number.

Click on an empty cell (1), and type in =COUNT(A4:A10) in the formula box (2).

return many items count formula

The lookup formula

Click on an empty cell (1), and 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))),"") (2), and then press F2.

Now hold CTRL + SHIFT simultaneously, and press enter.

empty cell long formula

 

Note: We used the small box on the right lower corner of the cell to drag down the result that is showing on the picture above.