ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

29Feb/120

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:

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

Matrix with banded categories

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.

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.