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


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:


Scrolling Dynamic Lookup

So, you’ve designed a great looking report or dashboard in Excel. But, wouldn’t it be great if you could add a small section of the report where the user could scroll through a filtered set of the raw data? Well, it’s funny you should ask...

Here we have some data I made earlier... a table of order data and a list of orders.

Sample data

What we’re aiming to do is create a magical Scrolling Dynamic Lookup window to add to our equally awesome report, like so:

Scrolling Dynamic Lookup


Complex Transpose

I’ve called this tip ‘Complex Transpose’, which hopefully describes what I’m about to show you. Essentially, it’s about transposing a two column data set of related items into a multi-row and column data set.

The key technique employed utilises Array formulas, which I won’t go into detail about, mainly because plenty before me have (see links below). The quick 'how-to' is that you need to hold down the CTRL & SHIFT keys when pressing ENTER to commit the formula, instead of just hitting ENTER. Their other common name is a ‘CSE formula’ (Control, Shift, Enter). When committed, the formula is surrounded by curly {brackets}.