Dealing with a single cell text list can be challenging in Excel. You cannot use VLOOKUP or XLOOKUP as search functions to returns results. Additionally, depending on the incoming data, re-configuring the data can take longer than its worth.
Let me explain the scenario in Excel. You have a kids sporting event and you have someone email you a list of the kids who attended practice that day. You copy the list from your email and hit Paste in Excel. Excel defaults this list into one cell. Your thinking great, now I have to retype all of the names into individual cells to track attendance. Wrong, let’s look at the example below.
In the example above, we want Excel to keep track of attendance based on the single cell text list from each day highlighted in Column B. If the Kids name is present in the list, the Cell under the kid’s name changes to Yes or No. Please note this dynamic feedback response can be changed to any response.
So how is this achieved? This can be done by using the following nested formula. In this scenario, we are using the IF Function with the nested COUNTIF Function.
=IF(COUNTIF($B2,""&C$1&"")>0,"Yes","No")
The equation looks at the list in cell B2, then uses parenthesis as a wildcard before and after the value in C1. If the value is present in the text list, it returns the feedback value “Yes.” If not listed, then “No.”
