Referencing multiple cells in different rows to give an output

[Ritik]

Hi! I am trying to link multiple calls in different rows with a formula I am using.

The formula I am using is: =IF(ISNUMBER(MATCH(DAY(F2),J28:M28,0)),"Holiday","")

Basically what this is doing is, that if cells J28 to M28 contain a value, it matches it to the date in cell F2, and if it is a match, this cell will contain Holiday.
This seems to work fine, however when I try to link multiple cells in different rows for it to search, it comes up with an error saying, "you've entered too many arguments for this function"

This is what I tried using when the error comes up =IF(ISNUMBER(MATCH(DAY(F2),J28:M28,J31:M31,0)),"Holiday","")

If you need any more information please let me know and I can provide. Thank you in advance.

 

[KRice]

It's not quite clear what you are trying to do. MATCH will "look" for the presence of the 1st argument (DAY(F2)) in the range passed to it in the 2nd argument (J28:M28)...and if you want an exact match to be found, then the 3rd argument is 0. So you can't pass two separate ranges to MATCH as shown. But what exactly do you want to do? Are you interested in knowing whether DAY(F2) is found anywhere in several non-contiguous ranges (such as J28:M28 and J31:M31)...and if so, then return "Holiday"...or do you want to know precisely where the match is found (e.g., specifically in L28)?

How many ranges are you searching? Is it just the two described?

 

[KRice]

I'm making a guess at what you want. Here I've entered a date into cell F2 so that DAY(F2) returns the day number (in this case 27). Then SUMPRODUCT is used to add together arrays formed from attempting to match DAY(F2) to the various ranges. If a match is found, SUMPRODUCT will return the number of matches, so if the value is >0, a match is found, hence "Holiday" is returned.

MrExcel_20220908.xlsx
	F			I	J	K	L	M
1
2	6/27/2022			Holiday
3
27
28					24	25	26	27
29
30
31					28	29	30	31
Sheet5

Cell Formulas
Range	Formula
I2	I2	=IF(SUMPRODUCT((J28:M28=DAY(F2))+(J31:M31=DAY(F2)))>0,"Holiday","")

 

[Ritik]

I'm making a guess at what you want. Here I've entered a date into cell F2 so that DAY(F2) returns the day number (in this case 27). Then SUMPRODUCT is used to add together arrays formed from attempting to match DAY(F2) to the various ranges. If a match is found, SUMPRODUCT will return the number of matches, so if the value is >0, a match is found, hence "Holiday" is returned.

MrExcel_20220908.xlsx
	F			I	J	K	L	M
1
2	6/27/2022			Holiday
3
27
28					24	25	26	27
29
30
31					28	29	30	31
Sheet5

Cell Formulas
Range	Formula
I2	I2	=IF(SUMPRODUCT((J28:M28=DAY(F2))+(J31:M31=DAY(F2)))>0,"Holiday","")

Thank you for your response this seems to work great, however could you explain to me why this works. As I thought SUMPRODUCT would do some sort of multiplication and sum. How is it, that it is able to search the the specified cells and return "holiday" if it matches the date in cell F2

 

[Ritik]

It's not quite clear what you are trying to do. MATCH will "look" for the presence of the 1st argument (DAY(F2)) in the range passed to it in the 2nd argument (J28:M28)...and if you want an exact match to be found, then the 3rd argument is 0. So you can't pass two separate ranges to MATCH as shown. But what exactly do you want to do? Are you interested in knowing whether DAY(F2) is found anywhere in several non-contiguous ranges (such as J28:M28 and J31:M31)...and if so, then return "Holiday"...or do you want to know precisely where the match is found (e.g., specifically in L28)?

How many ranges are you searching? Is it just the two described?

Yes I am interested in knowing weather Day(F2) is found anywhere in several non-continuous ranges and if it is found, then return "holiday"

 

[KRice]

SUMPRODUCT is performing only addition (no multiplication) because only one array is formed as its argument. That array consists of the sum of two separate arrays: the first is an array of TRUE and FALSE describing whether DAY(F2) is equal to anything is the range J28:M28. The second array is also one consisting of TRUE and FALSE values when considering the range J31:M31. When those two arrays are added (+) the TRUE's and FALSE's are coerced to 1's and 0's, so we are left with an array of 4 elements consisting of 1's, 0's, and possibly 2's (if DAY(F2) were found in both ranges), which is passed to SUMPRODUCT. But there is no other array to multiply this array with, so the last operation performed by SUMPRODUCT is to sum the array. For that matter, SUMPRODUCT could be trimmed down to SUM, as they are equivalent in this case (see below).

I was asking whether other ranges might be involved, as it might be more efficient to specify the comparison ranges differently. If the number of ranges is relatively small, I would opt for one of the approaches shown.

MrExcel_20220908.xlsx
	F			I	J	K	L	M
1
2	6/27/2022			Holiday
3				Holiday
27
28					24	25	26	27
29
30
31					28	29	30	31
Sheet5

Cell Formulas
Range	Formula
I2	I2	=IF(SUMPRODUCT((J28:M28=DAY(F2))+(J31:M31=DAY(F2)))>0,"Holiday","")
I3	I3	=IF(SUM((J28:M28=DAY(F2))+(J31:M31=DAY(F2)))>0,"Holiday","")

 

[KRice]

Edited: I forgot to ask...is F2 an actual date?...and are you dealing with only two ranges?

 

[Ritik]

Edited: I forgot to ask...is F2 an actual date?...and are you dealing with only two ranges?

Yes F2 is a date, but is a formulated using this - =WORKDAY(DATE(A2,A3,0), 1)
A3 has the month and A2 has the year.

and I may deal with more than 2 ranges, maybe around 4 or 5 ranges of data , such as J28:M28 , J31:M31 , J34,M34, J36,M36 ....

Sorry for the late reply!