Twitter
Facebook
Flickr
RSSThe Matrix
May 9
A while back, one of the guys at work came to me with an Excel problem… He had a matrix of data, and wanted a formula that would, given row and column criteria, return the appropriate value:
I initially thought about combining lookups with IF statements but it got *really* messy. A little digging revealed a pretty handy solution, which I thought I’d share with you all…
The answer lay in combining the MATCH and INDEX functions* – the former (used twice) to locate both the Role and Rating in the appropriate arrays, then the latter to locate the corresponding intersection:
MATCH(A18,$A$2:$A$8,0) searches the range A2:A8 for the role held in A18
MATCH(B18,$B$1:$F$1,0) searches the range B1:F1 for the rating held in B18
INDEX($A$1:$F$8,(MATCH(A18,$A$2:$A$8,0)+1),(MATCH(B18,$B$1:$F$1,0)+1)) locates the intersection. Note the “+1″ that deals with the header rows/columns in the INDEX array (A1:F8).
This works, but will return a #N/A! error if the referring cells are empty. Some people don’t mind this, but I prefer to clean things up a bit by using an additional IF statement and the ISNA function; effectively stating “If the index/match formula returns a #N/A! error, display a blank. Else, display the formula results”.
Now, this formula is a little unwieldy, so you can download the sample Excel file here.
Hope you find it useful!
*http://support.microsoft.com/kb/214142
