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.

image003

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

Posted in Uncategorized

9 thoughts on “Two Dimensional (i.e., Two Variable) Lookup

  1. Thanks for sharing. I can not figure it out. How do you create the header ‘product/category’ in one cell. I tried to find it by searching but was not succesful. I would appreciate a little hint.

  2. LOL! I knew that would be of as much interest, if not more, than the look up stuff itself. {grin}

    It’s kinda brute force.

    The diagonal line is a cell border feature.

    So, I put that in first.

    Then, I added Category ALT+ENTER Product. The ALT+ENTER inserts a new line in a cell.

    Then, I added a bunch of spaces before the C of the Category and resized the cell dimensions until I got the desired result.

    From what I recall, there is a more rational way to do this, i.e., without experimenting with the cell dimensions and the number of spaces.

    But, I couldn’t think what that might be. So, I went with the brute force approach.

  3. OK, nothing like writing “I don’t know” to stimulate the appropriate grey cells. {grin}

    The ‘rational’ way works in Word but not in Excel.

    In Word, create a table. Then, in a cell type Category ENTER Product. Now, format the Category paragraph as right-aligned and add the diagonal cell border.

    Unfortunately, in Excel, one cannot set differently the alignment of the text above and below the ALT+ENTER.

  4. Tushar, thanks for the quick info. I got it when you said ‘diagonal line is a cell border feature’ ;-) Thanks again.

  5. Gosh, 2D lookup like Lotus 123 had in Release 3 in 1989 with @XINDEX. Progress in Excel knows no bounds!

    How many newsgroup articles have there been about this over the decades? Not saying this isn’t useful, but it isn’t ground-breaking.

  6. I haven’t investigated making this into a formula but I discovered something about named ranges a couple of years ago and I’ve never figured out a good use for it.

    Create your table above but make a named range for each column. e.g. ColA, ColB, ColC and so on. Do the same for the rows: RowAA, RowBB, RowCC.

    Now, in another cell, type:
    =ColB RowCC

    You get the cell value at the intersection of the two named ranges.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

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