1

I have attach the excel file enter image description here

Need Array formula to multiple match different column with different rows and get result in EXCEL 2007

As you can refer to the picture I want to check Year between 2000 and 2010 along with Month, Day & Type common in all 11 rows i.e. 1, 1 & A

I need to check total 11 Rows having all same criteria from ROW 2 Y=2000, ROW 2 M=1, ROW 2 D=1, ROW 2 TYPE=A, Y=2001,CELL 249 M=1, ROW 249 D=1, ROW 249 TYPE=A, ........ROW 2480 Y=2010, ROW 2480 M =1, ROW 2480 D=1, ROW 2480 TYPE=A .....IF all these are satisfied only then Mark "X" Else ""

Improve this question
6
  • Sorry to say, this question just doesn't make sense. What's the situation where you don't need "X", it's if always day=1 month =1 and type =A regardless of year ? Why do you need array formula?
    – Máté Juhász
    Commented Dec 8, 2019 at 6:19
  • Why do you need an array formula? Why do you need to check the year?
    – Ron Rosenfeld
    Commented Dec 8, 2019 at 20:46
  • If you see in the image, in column D there are two values "A" AND "B" now what I want in Sell E2 is , that suppose in Year 2000 and in Month 1 and Day 1 and Column D = "A" , Like wise if all these conditions are Ture till Year 2010 only Then it displays "X" else it should leave blank
    – Siraj Multani
    Commented Dec 9, 2019 at 5:40
  • I need array formula coz, if then I Change from M=1 Day=1 to M=1 and Day=15
    – Siraj Multani
    Commented Dec 9, 2019 at 5:47
  • @SirajMultani,, please click this link edit & modify the criteria (that U need all 11 rows to test),, and please delete the New Question since it will be treated like DUPLICATE post and would attract down vote too !! –
    – Rajesh Sinha
    Commented Dec 10, 2019 at 3:33

1 Answer 1

Reset to default
1

Even a non array IF logical formula can solve the issue.

enter image description here

  • Formula in cell E46:

      =IF(AND(A46>=2000,A46<=2099,B46=1,C46=1,D46="A"),"X","")

N.B.

  • The first part IF(AND(A46>=2000,A46<=2099 checks year falls between 99 years of span, which would be at the maximum possible.
  • Since rest criteria are common Month, Day & Type should 1, 1 and A, then the IF combined with AND solves the issue.

Edited:

I've edited the post since OP's need is to check all 11 Rows for the said criteria.

  • An array (CSE) formula in any required cell:

     {=IF(AND(A$46:A$57>=2000,A$46:A$57<=2002,B$46:B$57=1,C$46:C$57=1,D$46:D$57="A"),"X","No Match")}

  • Finish the formula with Ctrl+Shift+Enter

  • Using current data (shared by OP ) this will return No Match.

  • But with my sample data it return X since row 51 meets all criteria.

Note: this may works as non array formula also, but as precaution better use as an array (CSE) formula.

Adjust year's value & cell references in the formula as needed.

Improve this answer
7
  • Please write your concern also that Y this has been down voted!!
    – Rajesh Sinha
    Commented Dec 9, 2019 at 4:34
  • your formula will only check the Value in 1 row, but I need to check total 11 Rows having all same cireteria from ROW 2 Y=2000, ROW 2 M=1, ROW 2 D=1, ROW 2 TYPE=A, Y=2001,CELL 249 M=1, ROW 249 D=1, ROW 249 TYPE=A, ........ROW 2480 Y=2010, ROW 2480 M =1, ROW 2480 D=1, ROW 2480 TYPE=A .....IF all these are satisfied only then Mark "X" Else ""
    – Siraj Multani
    Commented Dec 9, 2019 at 5:46
  • @SirajMultani you comment to this answer shows your question needs updating as it is not clear. The answer provided by Rajesh does answer your question as asked. You need to make it clear you need the search result to show an X if and only if ALL the dates meet your requirement.
    – Kevin Anthony Oppegaard Rose
    Commented Dec 9, 2019 at 6:46
  • 1
    @SirajMultani,, I've exactly answered as your post reflects (even the modified version is as similar as provious),, if you want to check Year between 2000 and 2010 along with Month, Day & Type common in all 11 rows i.e. 1, 1 & A the better Edit your post and make it clear,, you have no idea how much time we are investing to create proper answer and every day twist in the question simply spoils our efforts!!
    – Rajesh Sinha
    Commented Dec 9, 2019 at 7:10
  • Dear Rajesh, we really appriciate the effort you experts contribute to this site. Trust me for us to be on this site is like 1 stop solution. Thank you so much Mr Rajesh.
    – Siraj Multani
    Commented Dec 10, 2019 at 1:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .