Look Who it Was Random video

Excel - Efficient Excel Solution: Categorizing Items Based on Words in a Cell - Episode 1055

979 views · Published 10 July 2009 · 8:22 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Efficient Excel Solution: Categorizing Items Based on Words in a Cell.

Welcome to another episode of the Dueling Excel podcast, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel.com, go head-to-head to find the best solution to an Excel problem. In this episode, we tackle a question from Faizee about categorizing items based on the presence of certain words in a cell. Specifically, if cell C7 contains the words "paper" or "sticker", the category should be "paper/stick", otherwise it should be "card".

As I start to tackle this problem, I immediately think of using the FIND function to search for the word "paper" within the cell. However, I also need to account for the possibility of the word "sticker" being present. To do this, I wrap the FIND function in the ISERROR function, which will return a TRUE or FALSE value depending on whether the word is found or not. I then copy this formula down to all the cells and use it to determine if the word "paper" is present.

But this is where things get tricky. We also need to check for the word "sticker" and determine the appropriate category based on the combination of these two words. To help visualize this problem, I turn to my Electrical Engineering days and use a Karnaugh map to map out all the possible conditions. This allows me to see that the solution lies in using an AND condition to check if both the ISERROR FIND formula fragments are generating TRUE values. If they are, then the category should be "card", otherwise it should be "paper/stick".

While my solution works, Mike comes up with a much more efficient formula using the SEARCH function and array syntax. By asking the SEARCH function to look for both "paper" and "sticker" within the cell, he is able to use the ISNUMBER function to check for either of these words. This eliminates the need for the ISERROR function and simplifies the formula. Additionally, Mike also shows how this array syntax can be used in other formulas, such as finding the largest values in a range.

But the solutions don't stop there. Mike also demonstrates how the REPLACE function can be used to check for multiple words in a cell, and how the VLOOKUP function can be used to categorize the items based on the presence of these words. In the end, Mike's solution proves to be the most efficient and elegant, earning him a point in our friendly competition.

We hope you enjoyed this episode of the Dueling Excel podcast and learned some new tips and tricks for working with Excel. Be sure to tune in next week for another exciting episode where we will continue to battle it out to find the best solutions to your Excel problems. Thanks for watching and we'll see you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction by Bill Jelen and Mike Girvin
(00:25) Question from Faizee
(00:35) Explanation of the problem
(00:46) MrExcel's solution using FIND and ISERROR functions
(02:00) Explanation of Karnaugh map and its use in the solution
(03:34) Mike Girvin's solution using SEARCH and ISNUMBER functions
(06:40) Example of using array syntax without CTRL+SHIFT+ENTER
(07:50) Other possible solutions using OR and VLOOKUP functions
(08:01) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Array formula
Card category in Excel
CSE formula
Excel tip
FIND function
ISERROR function
Karnaugh map
Paper/sticker category in Excel
SEARCH function
VLOOKUP function
YouTube Excel tutorial

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152512/



Faizee from Youtube posts a question about looking for certain words in a cell in order to create a category. While this sounds simple, Bill and Mike manage to come up with a number of possible formulae in Episode 1055!

This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!

More from this channel