Microsoft Excel Tutorial - Wrap Wildcards around Lookup table in Excel.

In this video, we tackle a seemingly simple question that actually stumped me for a bit. A viewer, Chris, asked if there was a way to do a reverse wildcard search in Excel. This means searching for a specific word in one column and then looking back into another column to find a match. I'll admit, I tried all sorts of things that I thought would work, but none of them did. So, I turned to the SEARCH function and came up with a solution that may not be the most elegant, but it gets the job done.

To demonstrate this problem, I have a list of text in one column and corresponding categories in another column. The goal is to search for a specific word, such as "Apple," and have the corresponding category appear in a separate cell. After trying various methods, I found that using the SEARCH function, along with IFERROR and TEXTJOIN, gave me the desired result. The SEARCH function looks for a specific word within a larger string and returns the position of that word. By using this function in an array formula, we can search for multiple words at once and get a result for each one.

To make the formula more user-friendly, I added an IF statement to convert any VALUE errors into blank cells. This is then combined with the TEXTJOIN function to display the categories in a single cell, separated by commas. This formula may seem a bit complicated, but it's a combination of old-school functions that have been around for a while. And it gets the job done, which is what matters most.

I have to give credit to Chris for sending in this question and challenging me to find a solution. And as always, I encourage you to read the comments section below where other viewers may have come up with even better ways to solve this problem. Thank you for watching, and be sure to subscribe to our channel for more helpful Excel tips and tricks. See you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]

Table of Contents
(0:00) Use wildcard for the lookup range
(0:34) XLOOKUP, FILTER, COUNTIF failed
(0:54) SEARCH function on an array
(2:11) TEXTJOIN, IFERROR, and SEARCH
(3:15) Surprisingly difficult
(3:25) 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:
Excel COUNTIFS
Excel FILTER function
Excel formula
Excel IFERROR function
Excel old school formula
Excel SEARCH function
Excel SUMIFS
Excel TEXTJOIN function
Excel VLOOKUP
Excel wildcards
XLOOKUP
YouTube comments for better solutions

Join the MrExcel Message Board discussion about this video at [ Ссылка ]

VLOOKUP, XLOOKUP, and SUMIFS all allow a wildcard in the Lookup_Value. But today, Chris wants wildcards wrapped around the lookup table! After trying XLOOKUP, FILTER, and COUNTIF, I finally end up with TEXTJOIN, IF, and SEARCH. Do you have a better way?

свернуть