Forum Discussion
James818
Aug 15, 2023Brass Contributor
If one of multiple cells contain specific text then return value
I am stumped on work project. I have a cell F13 intended to be dependent on a range of 4 other cells. When all cells are No, I just need F13 to stay blank. So far this works. Range F5, F7...
PeterBartholomew1
Aug 15, 2023Silver Contributor
Just for entertainment, I returned each of the 4x6 array of assessment criteria with a single dynamic array formula. Then I aggregated the table to give the summary row with a second formula.
Worksheet formula to give 2D array
= IF(keyDates, IF((keyDates>=startDates) * (keyDates<=endDates),text,"No"), "")
Worksheet formula to give summary row
= IF(BYCOL(status=text, ORλ), text, "No")
Lambda function
ORλ
= LAMBDA(x, OR(x))
The key thing I would note is that current Excel formulas bear little to no resemblance to traditional spreadsheet methods.