Help wanted with an array function with many criteria

Copper Contributor

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

11 Replies

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).

@Ariiiiba123123 

That's an odd nesting of functions within the formula. A sample would help tremendously.

@Ariiiiba123123 

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

@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!

@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!

@Patrick2788 

 

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

I’ll defer looking into your sample file until such time that I receive feedback from you upon using my suggested formula.
Like you did, I’ll thereafter try looking into your sample file!

@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!

@Ariiiiba123123 

Please try to apply my suggested formula in your sample file, specify your expected result, then I'll see what I can do. 

@Twifoo I have tried your formula and it wrongly returns "No match". Potentially because the H and J columns are not always empty

@Ariiiiba123123 

Please enter the formula in the desired cell and specify what should be your expected result.