Feb 23 2020 01:15 PM
Feb 23 2020 01:15 PM
I would be eternally grateful if someone could take some time and assist me with sorting out an array function that I got.
=ArrayFormula(IF(WEEKDAY(G2)=2,INDEX('Merged sheet'!A3:A127,MATCH(1,AND((H6>'Merged sheet'!G3:G127),(H6>'Merged sheet'!I3:I127))*AND(IF('Merged sheet'!H3:H127>0,(H6<'Merged sheet'!H3:H127)),(IF('Merged sheet'!J3:J127)>0,H6<'Merged sheet'!J3:J127)),0))))
What I am trying to do is to pull names from column A in "Merged Sheet" based on criteria in the other columns mentioned. The issue I have is that the part (see bold above) where I require the values in the H and J columns (Merged sheet) to be non-empty (I used >0).
So I have two questions:
1.) Have I used the right logic for the issue at hand?
2.) What is wrong with my formula?
Thank you so much for your help!
All the best,
Feb 23 2020 02:29 PM
It would be difficult to digest this formula without any data. Perhaps you could share your workbook to help provide some context (Please remove any sensitive data - of course).
Feb 23 2020 03:06 PM
That's an odd nesting of functions within the formula. A sample would help tremendously.
Feb 23 2020 06:55 PM
If I understood the logic of your formula correctly, perhaps this non-array formula returns your desired result:
"Weekday is not Monday")
Note that the foregoing formula returns the name corresponding to the first match, if the weekday of the Date in G2 is Monday; otherwise, it returns the text "Weekday is not Monday". If no match is found, it returns the text "No Match".
You may have to precede dollar sign ($) to the row numbers of the references to the Merged sheet, if you intend to copy the formula down rows.
Feb 24 2020 12:34 AM - edited Feb 24 2020 12:36 AM
Thank you very much for your reply.
I have attached a sanitized version of my workbook. I hope it helps.
The relevant formula is in sheet "Background calculations", B4
Feb 24 2020 12:37 AM
@Twifoo Thank you for your reply! I had understood that I need array formulas for index+match formulas with several criteria? I will give your solution a try and see if I can get it to work. I have also uploaded a workbook in this thread, maybe that makes it easier to understand the issue!
Feb 24 2020 12:38 AM
Hi Patrick, thanks for your reply!
I am new with this so might be that I used the wrong formula or wrong nesting... Anyway, I uploaded a sheet for you to look at if you dont mind!
All the best
Feb 24 2020 10:33 AM
Feb 25 2020 05:02 AM
@Twifoo Dear Twifoo, thank you so much for taking your time, again. I did try out the formula you suggested me but I get a "No Natch" value in return although this should not be the case. I believe that the issue might be that the values where the cells should be <>0 should not return negative but rather positive, if that makes sense to you.
Let me just quickly describe what I want to do again as I believe I was not so clear about it before:
The chosen time has to be within the 2 slots outlined in the "merged sheet". With the >0 it is no issue if the cells are empty as it will return a positive value nonetheless; however, the <0 values are a problem as the empty cells will make the formula return a negative result, although any number should be returning a positive in that case, as the second slot does in that case not exist.
Let me know if I can clarify my intentions further!
Feb 25 2020 10:22 PM
Please try to apply my suggested formula in your sample file, specify your expected result, then I'll see what I can do.
Feb 25 2020 10:55 PM
@Twifoo I have tried your formula and it wrongly returns "No match". Potentially because the H and J columns are not always empty
Feb 26 2020 12:33 AM
Please enter the formula in the desired cell and specify what should be your expected result.