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 theDestinationsheet -
Destination: contains the category names and the formula that pulls the budget values from theSourcesheet
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.
If you have questions or anything to add, please share more in this thread…
Best,
Randy
