Jacob J. Walker's Blog

Scholarly Thoughts, Research, and Journalism for Informal Peer Review

Archive for April 18th, 2014

A use for “Data Tables” in Excel

with one comment

One of the features of Excel that I really have never liked is that of “data tables”.  First, they sound too much like “tables” which is a newer feature that really is cool (yet, surprisingly my experience has shown that many otherwise knowledgeable Excel users still don’t know about them).    The other problem with the data table, is that it was really meant to be a what-if tool, but I have not found a situation yet, where I couldn’t create the equivalent of a data table through the use of mixed references within a formula, and the method of setting up a data table has always been tricky, as they use an array function.  (Yet unfortunately, they are still commonly taught, and Microsoft includes them in their advanced certification test.)

But, with my critique of the official data tables feature aside, the concept of having a table that includes numbers as headings to both the rows and columns can be quite useful at times.  And recently I ran into a very good use for this configuration, which is that I wanted to have a table to lookup some data within, but I needed to use 2 variables.  I usually solve this sort of problem by making a compound key by concatenating 2 or more fields into the first field, and then using a VLOOKUP with range_lookup set to FALSE.  But in the case of my problem today, I needed to have both variables be able to do an approximate lookup (in other words, find them within a range of values.)

So I solved this by making a group of data in the form of a data table (although I didn’t use the data table or =TABLE() function, nor did I make it an Excel table), where I had one variable as my row labels, and another set as my column labels.   I then combined an INDEX() formula with 2 MATCH() formulas within it, both of the match formulas having a match_type set to 1.

I know this probably doesn’t make sense to those who aren’t die hard Excel folks…  But, I have been working on a set of Libre Excel Tools for Data Science, which I’m putting together User Defined Formulas to make this sort of thing easier, and with my recent experience, I plan to create a User Defined Formula (UDF) that makes this easier to do.


Written by Jacob Walker

April 18th, 2014 at 11:59 am