How to create many to many lookup?

This tutorial article is about how to create a many-to-many lookup in Excel. It is something that begins with this data:

data table

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

looking value

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