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
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: contains the category names and the formula that pulls the budget values from the
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
The lookup formula looks like this:
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
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.
B$1) in the range of the second parameter (i.e.
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
Anyhow… I hope this is helpful.
If you have questions or anything to add, please share more in this thread…