How to check one single cell value is matching with range of intermittent cells value

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

How to check one single cell value is matching with range of intermittent cells value

Post by BOOBALAN.V »

Dear Team,

I am facing this issue to check month and year of Cell A2 is matching with every third cell's month and year range from F2:L2. If it is matching i need to get result true. But since range of cells are having mixed data like numbers and alphanumeric, i am getting #NUM! error. Could you please help me.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78351
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to check one single cell value is matching with range of intermittent cells value

Post by HansV »

Try this formula:

=AND(IF(MOD(COLUMN(F2:L2), 3)=0, F2:L2-DAY(F2:L2)=A2-DAY(A2),TRUE))

If you do not have Microsoft 365 or Office 2021, you may have to confirm the formula by pressing Ctrl+Shift+Enter.
Best wishes,
Hans

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: How to check one single cell value is matching with range of intermittent cells value

Post by BOOBALAN.V »

Logical idea behind this formula is great sir. But, why we couldn't skip that intermittent cells sir :scratch: i thought we could achieve it by array formula like getting cells only {F2,I2,L2} based on condition and checking each value with A2 cell. I tried in different ways before posting here. To learning purpose i am asking sir. Thank you sir.

User avatar
HansV
Administrator
Posts: 78351
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to check one single cell value is matching with range of intermittent cells value

Post by HansV »

A formula such as =(F2,I2,L2)=A2 returns an error...
Best wishes,
Hans

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: How to check one single cell value is matching with range of intermittent cells value

Post by BOOBALAN.V »

Got it sir. Thank you.