When I was a housing developer I used Excel for budgets and pro formas. I wrote PPMT formulas, did lots of Goal Seeking, and never used pivot tables. Now I use them all the time, and my favorite formula is an Index/Match. It’s gotten to where I can type a fancy lookup pretty quickly, though my array formulas generally take a bit of hacking. I’m no Barry Houdini, but I do all right.

Yesterday I realized you can do a Match into an array of substrings of cell values. For example, in this baseball stats sheet (I think it’s “rhubarbs” per year) you can match against just the beginning few characters of each column heading, like “SF” or “STL.”

Here’s the formula in cell I3. Note that it’s an array formula, entered with Ctrl-Shft-Enter:

• The first part, “INDEX(Table2”, says to index into the whole table. In other words, it’s a two-dimensional lookup.
• The second part, “MATCH($G$3,tblRhubarb[YEAR],0),” says to look in the year column for a match to the year in G3.
• The third part, “MATCH(TEXT($H$3,”0″),LEFT(tblRhubarb[#Headers],LEN($H$3)),0))” says to look in the column headers for the team abbreviation in H3.

I wasn’t sure that last section would work. It says to look only in the left part of each header. When you analyze this last part by highlighting it and hitting F9 it looks like…

… which is pretty cool.

The final thing to mention is about this part of the formula:

This says to look at the leftmost number of characters equal to the length of the string in H3. That’s important because not all the team abbreviations are the same length. It would be important even if they were, because you don’t want to rely on “magic numbers” in formulas or code.

If you’re interested in these types of formulas be sure to go back to the top of the post and click the barry houdini link. Not only does he have a great name, he could write a formula that would find your car keys, and it wouldn’t even have to be array-entered.