Two Dimensional (i.e., Two Variable) Lookup

There have been several requests in various forums to look up data in a two dimensional (2D) table. This documents a couple of the more typical requests.

The first requirement is to find the entry at the intersection of a particular value of the first column and the first row. For example, look up the value for Product KK and Category F.

The second requirement is to find, given the value for a product, the category with the minimum value. The example below looks up the minimum value for Product JJ and then finds the corresponding category, G.

There are variants of this requirement that have the same solution adjusted as required. It could be to find the product that yields the minimum value for a particular category, i.e., go in the opposite direction of the previous example.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0106%202D%20lookup.shtml

Tushar Mehta

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>