Looking for non-array (preferred) formula to look into A2:E294 and extract values into M2:Q19 if conditions in I2, J2:J3 and K2:K3 are met. Note: When you check one of the check boxes in G2:G5, column I will be populated. If you select 2 check boxes, column I will populated as I2:I3 and the formula should pull based on this.
Desired outcome is in M2:Q19.
See sample file
Thanks
1) Could you manual input the expected results for second "What", i.e, "MM"?
2) Non array formula, it should require helper columns. Is it allowed?
Quang PT
bebo021999:
Non array formula, it should require helper columns. Is it allowed? Yes.
Could you manual input the expected results for second "What", i.e, "MM"? No. You have to check the check box for MM. If you check the check box for MM, you will have MM and Level - B in column I2:I3 and the non-array formula will have to pull values for both MM and Level-B.
See sample file.
Thanks
Last edited by bjnockle; 07-06-2021 at 11:18 PM.
This proposal employs a helper column (F) which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using: =SUMPRODUCT((I$2:I$5=A2)*(I$2:I$5<>"")+(J$2:J$5=C2)*(J$2:J$5<>"")+(K$2:K$5=E2)*(K$2:K$5<>""))=3
The range M2:Q19 is populated using: =IFERROR(INDEX(A$2:A$294,AGGREGATE(15,6,(ROW($A$2:$A$294)-ROW($A$1))/($F$2:$F$294=TRUE),ROWS($A$1:$A1))),"")
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Because of the tables and information you provided, it is obvious that array formulas must be used, and helper columns must be added to non-array formulas.
Provide array formula solutions , Cell M2 array formula , Drag down and across
HTML Code:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks