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
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.
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.
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.
Tushar, thanks for the quick info. I got it when you said ‘diagonal line is a cell border feature’ ;-) Thanks again.
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.
Hi,
The two dimensional lookup was available in Lotus 123 (remember that product?) as XINDEX.
Below is a link to my page that deals with this:
http://www.bygsoftware.com/Excel/functions/sumproduct.htm.
Cheers
Andy
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.
Did you consider using offset:
The value in column in B2 with the cross sign how we enter that