Now we tell OFFSET how many columns to move right or left.
This is determined by the user’s Division selection from the first drop down list.
For this, we can use the MATCH function to locate the selected Division in cell A5 from the list of Division in cells F4 through H4. We also need to match exactly (0 in the last argument).
=OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0)
NOTE: We only “locked” the “A” part of the “A5” reference because we don’t want to point to other columns when we replicate this to adjacent cells, but we do want to point to other rows when creating additional drop downs. If that’s a bit unclear, stick with us because it will become clearer very soon.
If we highlight the MATCH portion of the formula and press F9, we can see MATCH returns a number indicating the discovered position in the search range.
NOTE: Remember to press CTRL-Z to undo your formula back to its original structure.
The Problem with MATCH
A minor issue we have with MATCH is that it counts cells beginning with the value 1.
This means that “Productivity” is in the first column of the data, “Game” is in the second column, and “Utility” is in the third column.
Because we’re trying to inform OFFSET how many columns to move to the right, these values are incorrect.
If we want “Productivity”, we don’t want to move left or right. If we want “Game”, we want to move 1 column to the right, and if we want “Utility”, we want to move 2 columns to the right.
We need to reduce the returned value of MATCH by 1 to adjust for this discrepancy.
=OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) – 1