Single Cell Text List with Dynamic Response

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.

Text Name List in one Cell
Text Name List in one Cell

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.

Dynamic Feedback
Dynamic Feedback

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.”

Leave a Reply

AVERAGEIF Function

AVERAGEIF (Arithmetic Mean) – Single Criteria IF statement Use the AVERAGEIF Function to find the arithmetic mean in Excel while

Read More »

Autofilter in Excel

AutoFilter Excel offers an easy way to search or modify complex data sets using the AutoFilter feature. In order to

Read More »
Scroll to Top