Okay, so I finally got around to writing another post! Must try harder in 2012.

This tip was sparked by a colleague’s question the other day, and since I’d recently used the technique in a risk register tool, I thought there might be many others out there wondering how best to lookup matrix values.

The premise is that you have a two dimensional matrix of values and you want to retrieve a value from the matrix, given that you know the input values for the two dimesions of the matrix.

So, here’s your matrix..

and here’s what your after in your results table..

The method uses a combination of the INDEX and MATCH functions to give:

=INDEX(MatrixRange,MATCH(RowValueLookup,RowCategories,0),MATCH(ColumnValueLookup,ColumnCategories,0))

You also might have the situation where you have a numerical scale that you classify with banded categories (instead of fixed categories). In this situation your matrix might look like this..

For the solution to the second problem check out the attached file below. Looking at how both types of matrix lookup are implemented will explain it far better than me rabbiting on about it… i.e. I’m being lazy.

Download “Matrix Lookup” Matrix_Lookup.xls – Downloaded 585 times – 25 KB