-2

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.

Screen Shot

  • 1
    Apart from multiple headers, within a header you have multiple values, e.g. For February it starts in D10 and ends in M11, so which values should be returned? – patkim Dec 27 '18 at 5:13
  • Are there more conditionals? – Lee Dec 27 '18 at 7:26
  • 1
    You have ten data columns for each month, and two key values, Pearl District and Hollywood, associated with all of the data. When you enter January, how is Excel supposed to know which of the 20 January values to select? There's a lot of missing explanation needed to understand the requirements. – fixer1234 Dec 28 '18 at 2:51
0

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,

Animated worksheet screen-cap showing the lookup Month being modified, the lookup Branch being modified, plus the formula being filled to the right

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

enter image description here

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 image description here

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

  • Just write your concern,, Y down voted ?? – Rajesh S Dec 28 '18 at 7:26

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.