Check for any match between three ranges but ignore zeros

[Louis chiron]

Hi, I'm looking for some help on this one.

I have a sheet with three ranges of random values. I need to compare the values in each range and check whether any value (other than zero) is present in all three ranges.
I don't need to know what the matching value is, or how many matches there may be, just that a match does exist.

In the example above, 32 appears in each range so I would like the formula to return a TRUE.
Zero also appears in each range but I don't want the formula to search for matching zero's so, if 32 wasn't present, it would be returning FALSE.

I managed to get it to work with the first two ranges using:
SUMPRODUCT(COUNTIF(A2:A7,B2:B7))

...but that wont work with three ranges, and it didn't ignore any matching zeros.

Is there a way to do this?

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is it possible that the same non-zero value can occur more than once in a single column? For example, with your sample data, would it be possible for B6 to be 32 as well as B4?

 

[Louis chiron]

Book3
	A	B	C
1	Customer ID	Year	Value
2	AA1	2019	32
3	AA1	2019	78
4	AA1	2019	0
5	AA1	2020	65
6	AA1	2020	32
7	AA1	2020	5
8	AA1	2020	5
9	AA1	2021	9
10	AA1	2021	32
11	AA1	2021	56
12	AA2	2019	12
13	AA2	2019	7
14	AA2	2020	7
15	AA2	2020	5
16	AA2	2021	65
17	AA2	2021	0
18	AA3	2019	26
19	AA3	2019	0
20	AA3	2020	0
21	AA3	2020	9
22	AA3	2020	9
23	AA3	2021	54
24	AA3	2021	26
25	AA3	2021	9
26	AA3	2021	0
Sheet1

Apologies, I've now updated my profile and installed XL2BB.
The sheet here shows the actual structure of my sheet - i refined it in my original post to avoid confusion. As you can see the three ranges of values are all sat in the same column.

I will already know the range address for each range i need to search through ( e.g. AA1's ranges are B2:B4, B6:B8 and B9:B11) so don't need to find these.

Column A is the unique customer ID. Each customer will have a range of values split into three years (Column B). Column C are the values for those years. The number of values in each year range will vary (e.g. customer AA1 has 3 values for 2019, but AA2 has 2 values).
For each customer on my list (approx. 20k customers), I am wanting to search for a match in the three 'year' ranges for each customer.
In my example customer AA1 has a match across the three years, but AA2 doesn't. AA3 has only a match on '0' but i dont want to ID any matching '0's.
It is possible for the same value to appear in each year range (e.g. '9' in AA3 2020)

 

[Louis chiron]

Not sure if i can edit a post but i should ask that the results i'd be looking for would be:

AA1 TRUE
AA2 FALSE
AA3 FALSE

 

[Louis chiron]

EDIT: "I will already know the range address for each range i need to search through ( e.g. AA1's ranges are C2:C4, C6:C8 and C9:C11) so don't need to find these."