Matrix Lookup

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.. 

The matrix of values

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

Matrix lookup results

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