 # Quick Formula Tip: the 2-D Lookup

Sometimes our lookups need to evaluate both column and row headers to find the appropriate value.

In fact, this situation came up in the new Budget Builder template… The budget builder creates a month-by-month budget, but the sorted category order (likely) won’t match the `Categories` sheet. To move the values to the appropriate budget month in the `Categories` sheet required a two-dimensional lookup that matched the category name and also the budget month before pulling a value into the `Categories` budget.

I shared a formula in the wiki instructions, but I thought it might be helpful to describe how it works so it can be ported to other scenarios…

I made a simple demonstration of this two-dimensional lookup in a sheet you can copy here.

The demonstration spreadsheet contains two sheets:

• `Source`: contains budget values in a table with sorted category names from the `Destination` sheet
• `Destination`: contains the category names and the formula that pulls the budget values from the `Source` sheet

You can see that the category order (column `A`) is different between the two sheets. You can also see that the column headers to not match up 1:1. The formula below will figure out exactly where your data goes when moving it from the `Source` to the `Destination` sheet.

The lookup formula looks like this:
`=IFERROR(OFFSET(Source!\$B\$2,MATCH(\$A2,Source!\$A\$2:\$A,0)-1,MATCH(B\$1,Source!\$A\$1:\$1,0)-1))`

We can break this down easier if we unnest the three key formulas needed: `IFERROR()`, `OFFSET()` and `MATCH()`

``````1:=IFERROR(
2:  OFFSET(
3:    Source!\$B\$2,
4:    MATCH(\$A2,Source!\$A\$2:\$A,0)-1,
5:    MATCH(B\$1,Source!\$A\$1:\$1,0)-1
6:  )
7:)
``````

Let’s start with `IFERROR()` (lines 1 & 7)… `IFERROR()` is a wrapper that cleans up formula errors. In this case, if a category- or date-match is not found, you would see an ugly in-cell error (e.g. `#N/A`). When these errors occur within an `IFERROR()` wrapper, the `IFERROR()` formula allows you to define what data or formula should be presented instead. If no alternate content is provided (as in our formula), the cell is left blank. (This formula makes the budget cells blank in rows 7 & 8 where no category is defined in the `Destination` sheet.)

Next up, the `OFFSET()` formula allows you to start with a reference to a cell and shift that reference by a number of rows or columns. In our case, we want to fetch data out of the budget values area of the `Source` sheet, so the anchor cell for our `OFFSET()` formula is `Source!\$B\$2`. The question is how many rows should we shift to up-and-down and right-and-left to find the matching value. That is the work of the two subsequent formula parameters…

Finally, we use the `MATCH()` formula to find out (first) how many rows down the matching category name is, then how many columns across the matching budget month (header) is. The `MATCH()` formula looks for a match of the value in the first parameter (i.e. `\$A2` or `B\$1`) in the range of the second parameter (i.e. `Source!\$A\$2:\$A` or `Source!\$A\$1:\$1`). You can see that the first two arguments are appropriately “money bagged” so the cell references can be dragged. When the final/third parameter is zero, the `MATCH()` formula looks for an exact match. We subtract one from each `MATCH()` result so the reference does not shift when the match is in the first row or column (e.g. a match in the first cell returns a value of `1` and we want a value of `0`). (A more clever implementation would start the offset cell reference in `Source!\$A\$1`, obviating the need for decrementing the `MATCH()` result.)

Anyhow… I hope this is helpful.