Forum Discussion
Match a tag name
=IF(SUM((--ISNUMBER(SEARCH(","&[@Tags]&",",","&Table13[Tags]&",")))*("yes"=Table13[EOL]))>0,"yes","")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula returns "yes" if there is at least one device with the tag from column A of the Runbook Meeting Schedule and "yes" in column EOL of the Planning list.
For example there are two devices which run on EOL and have the tag ACT.
=IF(AND(OR(ISNUMBER(SEARCH($A$2:$A$209,B2))),ISNUMBER(SEARCH(",",B2))),"Yes","")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
OliverScheurich - it worked up until Row 72. Because my “A” column goes to row 209 and “B” only has data up until row 72 the remaining of “A” was not compared.
For example the entry of cell A204 "WhatsUpGold" is found in cell B68 and the formula returns "yes" in cell C68. Isn't this the expected result?
Sorry for the confusion. let me try to explain what I am trying to do.
Within the newly attached sheet I have provided my real-world example.
The Data Explained:
- Within the Runbook Meeting Schedule I have a list of Applications (Tags) listed in column "A".
- Within the Planning list has in the "D" (Tags) column is the list of applications running on devices in the corresponding "A" cell (Device). Within the "N" column is a "Yes" or blank, which tells me if "Yes" then the Operations systems listed in "F" is EOL.
The Requirement:
Within Runbook Meeting Schedule column "B" I would like a formula to match the Tags listed in Runbook Meeting schedule column "A" to find the same App name in the PlanningList column "D" (Tags) and if there is a "Yes recorded in "N" (EOL) then add a "Yes" back into the corresponding cell "B" back in the Runbook Meeting schedule.=IF(SUM((--ISNUMBER(SEARCH(","&[@Tags]&",",","&Table13[Tags]&",")))*("yes"=Table13[EOL]))>0,"yes","")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula returns "yes" if there is at least one device with the tag from column A of the Runbook Meeting Schedule and "yes" in column EOL of the Planning list.
For example there are two devices which run on EOL and have the tag ACT.