Feb 23 2020 01:15 PM
Hello everyone,
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,
Roger
Feb 23 2020 02:29 PM
Hello @Ariiiiba123123,
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:
=IF(WEEKDAY(G2)=2,
IFNA(LOOKUP(2,1/FREQUENCY(1,
('Merged sheet'!G3:G127<H6)*
('Merged sheet'!H3:H127<>"")*
('Merged sheet'!H3:H127>H6)*
('Merged sheet'!I3:I127<H6)*
('Merged sheet'!J3:J127<>"")*
('Merged sheet'!J3:J127>H6)),
'Merged sheet'!A3:A127),
"No Match"),
"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
@PReagan Hi!
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
Again, thanks!
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.