# ProfessionalExcel.comIn 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.

The matrix of values

Matrix lookup results

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

22Jul/111

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

13Jul/116

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