How can I compare a range of dates in two columns to find if a date exists in both or only one column?

I have a range of dates, and i need to compare if the date exists in two columns:

IF E6 exist in range AK6:AK24 and E6 exists in range AL6:AL24, returns "Booth"
IF E6 exists in range AK6:AK24 only, returns "Column 1"
IF E6 exists in range AL6:AL24 only, returns "Column 2"

So, set this up as you did not supply useful data:

IF(AND(IFERROR(MATCH(A1,M3:M7,0),0),IFERROR(MATCH(A1,N3:N7,0),0)),"both",IF(IFERROR(MATCH(A1,M3:M7,0),0)>0,"col 1",IF(IFERROR(MATCH(A1,N3:N7,0),0)>0,"col 2")))

For col 1 only and both:

Here is an alternative formula using INDEX() & XMATCH() with the help of BOOLEAN LOGIC

• Formula used in cell E7

=IFERROR(INDEX({"Column 1","Column 2","Both"},
XMATCH(SUM(($AK$6:$AK$24=E6)+(E6=$AL$6:$AL$24)*2),{1,2,3})),"")

To Explain how the formula works look at the example screenshot:

Formulas in the example screenshot :

• in cell AP7

=SUM(($AK$6:$AK$24=AO7)+(AO7=$AL$6:$AL$24)*2)

• in cell AQ7

=XMATCH(AP7,{1,2,3})

• in cell AR7

=IFERROR(INDEX({"Column 1","Column 2","Both"},AQ7),"")

• The SUM() within XMATCH() returns either 0 or 1 or 2 or 3 based on the BOOLEAN LOGIC

=SUM(($AK$6:$AK$24=E6)+(E6=$AL$6:$AL$24)*2)

• The XMATCH() returns the same position but it helps when there is no match, and hence returns #N/A

=XMATCH(SUM(($AK$6:$AK$24=E6)+(E6=$AL$6:$AL$24)*2),{1,2,3})

• Lastly we are wrapping it within IFERROR() to avoid errors and returns as blank while the INDEX() to get the desired output as needed.

=IFERROR(INDEX({"Column 1","Column 2","Both"},
XMATCH(SUM(($AK$6:$AK$24=E6)+(E6=$AL$6:$AL$24)*2),{1,2,3})),"")

Alternatively, you can also use MATCH() function if you don't have XMATCH() in your version of Excel.

=IFERROR(INDEX({"Column 1","Column 2","Both"},
MATCH(SUM(($AK$6:$AK$24=E6)+(E6=$AL$6:$AL$24)*2),{1,2,3},0)),"")

Note: Based on your excel version need to press <kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd> when using INDEX() with MATCH()

However, if you are in MS365 and want a spill effect then you can do it, with one single formula using BYCOL()

• Formula used in cell E7

=BYCOL(E6:AI6,LAMBDA(x,
IFERROR(INDEX({"Column 1","Column 2","Both"},
XMATCH(SUM(($AK$6:$AK$24=x)+(x=$AL$6:$AL$24)*2),{1,2,3})),"")))

Here is one more alternative, using the CHOOSE() Function with BYCOL()

• Formula used in cell E7 --> Needs to fill right!

=IFERROR(CHOOSE(SUM((($AK$6:$AK$24=E$6)+($AL$6:$AL$24=E$6)*2)),"Column 1","Column 2","Both"),"")

Or, if you want a spill array then

=BYCOL(E6:AI6,LAMBDA(x,
IFERROR(CHOOSE(SUM((($AK$6:$AK$24=x)+($AL$6:$AL$24=x)*2)),
"Column 1","Column 2","Both"),"")))

Few Test Cases for the above formula is working as per the requirements:

• in cell AS7

=IFERROR(CHOOSE(SUM((($AK$6:$AK$24=AO7)+($AL$6:$AL$24=AO7)*2)),"Column 1","Column 2","Both"),"")