Using INDEX/MATCH to return a value from a set of tables

Question

I'm having a hard time writing an INDEX/MATCH formula. I need the formula to look through multiple rows to find the one that matches my lookup value. I have multiple headers for different months.

The current formula I have in cell B2 is:

=INDEX($D5:$M5,MATCH($B$1,$D$4:$M$4,0))

As you can see, if I type January in B1, B2 displays a. If I type FebruaryI would like it to display 99.

Answer 1

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 B3:

=INDEX(D4:D16,MATCH($B$1,$D$4:$D$16,0)+MATCH($B$2,$C$4:$C$16,0)-1)

Explanation:

Instead of indexing into a row, the formula indexes into the required data column, i.e. D4:D16.

The first 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 -1.)

Notes:

• 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.)

Answer 2

My answer have two options:

1. Using Data Set shown in Screen Shot.

2. Solution with modified Data Set.

Option 1:

I would like to suggest a method, which comprises a simple trick to manage different data sets according to the Month.

How it works:

• You need to create Helper Cell in B41, which reflects Month's Name in Cell B42.
• Formula in Cell B42:

=IF(OR(B41=1,B41=2,B41=3,B41=4,B41=5,B41,6),CHOOSE(B41,"January","February","March","April","May","June"))

• Formula in Cell B43, drag it Down.

=IF($B$41=1,INDEX($B34:$L34,MATCH(B$42,$B$33:$L$33,0)),IF($B$41=2,INDEX($B38:$L38,MATCH(B$42,$B$37:$L$37,0)),""))

Note:

• Second Formula need to be extended as soon rest of month's data included.

Option 2:

• Enter this Formula in Cell B51 & fill it Down

  =INDEX($B47:$G47,MATCH($B$50,$B$46:$G$46,0))

Adjust cell references in the Formula as needed.