I am trying to filter my data based off of a cell value. My data will be a large range for 12 months. So far I am able to filter only one month, I cannot figure out how to filter all 12 months based off my cell value. Here is the formula I am using =INDEX(B6:B25,MATCH(B50,B4:AP4,0)) I have included a link to my file for better clarification. My MATCH cell is $B$1 and my formula is placed in cell $B$2

Here's my link

Thank you everyone for your help

  • Do you try the advanced filter? – Lee Nov 27 '18 at 9:52
  • Adding a link to the worksheet is very useful, but you must also include a screenshot. If the link breaks/file is deleted (like has happened), then the question may become useless/poor quality (as is the case here given that the only answer doesn't use your original data/cells). Note that, unlike what is stated in that answer, it is possible to do whatever it is your are trying to do with a single formula, without using any helper cells. – robinCTS Dec 30 '18 at 15:30
  • 1
    It looks like the dropbox link is dead. Without that and a screenshot, the question isn't clear enough to be helpful to other readers (which is kind of the purpose of the site). – fixer1234 Dec 31 '18 at 6:09

I've constructed this solution on the basis of the example shown in attached Screen Shot.

In your formula =INDEX(B6:B25,MATCH(B50,B4:AP4,0)) you are trying to match Months are in different Row intervals is not looking possible to accommodate with a single Formula.

enter image description here

How it works:

  • Write Month's Name in Cell F46, G46 & H46.
  • Write this Formula in Cell F47 & fill it Right.



  • This Formula will return only the First match value for every Month.

  • You need to extend the Lits of Months in Row 46 as well as the Formula in Row 47.

Adjust Cell references in the Formula as needed.

  • thank you! Your formula does what I needed to do! – Jose Cortez Nov 27 '18 at 20:13
  • @JoseCortez, glad to help you plzz keep asking ☺ – Rajesh S Nov 28 '18 at 6:29
  • Just write your concern Y down voted ?? – Rajesh S Dec 31 '18 at 5:17
  • sorry i did not downvote your answer – Jose Cortez Jan 3 at 7:51
  • it's okay @JoseCortez,, since the post got Down vote then I'm eager to learn the reason to improve it ☺ – Rajesh S Jan 3 at 7:53

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.