I work on a table with some cells, the value in “E” might be repeated once or more, but depending on the date value in column “H”, I need to get specific text. But, I don’t know how to write the formula.
What I need to do is:
IF) TOW CELLS HAVE THE SAME VALUE IN COLUMN"E" AND $H>TODAY(),"Valid Stream"
I wrote this formula, but it doesn’t work:
IF(AND($E5=E:E,$H5>TODAY(),"Valid Stream"))
I don’t need to check only “E5”, but if any cell in column “E” is repeated, and because it might be repeated many times, I need to control that with the date in “H” if it’s bigger than today().
“E5” is the first cell in the table.
12
Do you mean result like this?
A formula in J5:
=IF((COUNTIF($E$5:$E$12,$E$5:$E$12)>1)*($H$5:$H$12>TODAY()),"Valid stream","")
It is a dynamic formula in a new Excel, in earlier versions requires Ctrl+Shift+Enter.
Another variant requires all dates to be >=TODAY().
A formula in J5:
=IF(COUNTIFS($E$5:$E$12,$E$5:$E$12,$H$5:$H$12,">=" & TODAY())>1,"Valid stream","")
3rd attempt. Formula for a table and with requirement for all dates to be >=TODAY()
=IF((COUNTIFS([Code],@[Code],[Issue Date],">=" & TODAY())>1)*(COUNTIFS([Code],@[Code],[Issue Date],">=" & TODAY())=COUNTIF([Code],@[Code])),"Valid stream","")
18
In order to check if a cell (like “D1”) matches any cell, like in column “E”, you might do the following:
=ISNUMBER(MATCH("$D$1";E:E;0))
(where 0 stands for “exact match”)
Obviously, you might replace the entire column “E:E” by more precise values, like “E$5:E$3000”, but beware: you will always find “E5” in “E5:E3000” 🙂
1