Tom’s Tutorials For Excel: Intersecting Lookup With Multiple Criteria


Share Button

Tom’s Tutorials For Excel: Intersecting Lookup With Multiple Criteria

I received an interesting comment and request from a vistor named lohhw3, who asked a follow-up question about this tutorial for intersecting lookups. The question was how to look up a value in a table when there are two columns of criteria (Operations and Year in this example) in addition to the row header criteria for Month.



In the picture, two columns need to be considered in order to determine the correct row. With the criteria specified in range A24:C24, the formula in cell A27 that returns the correct value is
=INDEX(A1:N19,MATCH(1,(A1:A19=A24)*(B1:B19=B24),0),MATCH(C24,$A$1:$N$1,0))

Note that this is an array formula, meaning you must commit it to the cell by pressing Ctrl+Shift+Enter, not just with Enter.

Thanks to lohhw3 for the excellent question!

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*