Looking at your previous questions, it looks like you are trying to extract the data for a specific column given two lookup criteria - the Month and the Branch.
For simplicity's sake, I'm also assuming that the Branches are exactly the same for each sub-table, i.e they all exist and are in the same order.
With your worksheet modified as follows,
enter the following formula in
Instead of indexing into a row, the formula indexes into the required data column, i.e.
MATCH() finds the matching Month row, whilst the second one finds the offset to the matching Branch. The
-1 is required to convert the one-based index returned by the second
MATCH() to a zero-based offset. (And yes,
MATCH($B$2,$C$4:$C$16,0)-1 could have been replaced by
MATCH($B$2,$C$5:$C$16,0), but it is easier to understand with the
- The way the formula has been written means that filling the formula to the right extracts the next column's value. That's why the
D4:D16 is a relative reference.
- Alternatively, to obtain another column's data, just replace the
D4:D16 with the appropriate reference. (The absolute reference,
$D$4:$D$16, needs to remain unchanged if a single Month header is used, as explained next.)
- Multiple headers for each month are not required, even when extracting data from a column other than the first one. Only the first data column needs one or, alternatively, a merged header can be used instead. (This is why the
$D$4:$D$16 is an absolute reference.)