Perform a wildcard Lookup (or Sum) – Find values containing text

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Sometimes you want to lookup only part of a text value within a range/column-in the above example we might want to lookup anything containing the word ‘Sales’ in the department column and return the corresponding value in ‘Quantity Sold’. This would allow us to pick up ‘Digital Sales’, ‘New Technology Sales’ etc.

This is achieved by combining the substring we want to find (e.g “sales”) with a wildcard character like * (written as “*”)

Note that a lookup formula will return only the first matching value in this case-if instead we wanted to get the total quantity sold by all the sales-related departments (sum of quantity sold where department contains ‘Sales’) then we can apply the same concept to a SUMIF/COUNTIF] or COUNTIFS/SUMIFS formula

A wildcard is a special character which you can use to indicate one (specific) value or any series of values before or after some text. For example the wildcard character * indicates anything so the text “sales*” indicates any text which begins ‘sales’ whereas “*Sales” indicates any text ending with ‘Sales’ and “*Sales*” indicates any text containing ‘Sales’.

There are other wildcard characters you can use (Including searching for specific characters)- see the links at the end of this topic for further information. It’s worth noting that you can use Wildcards in some other find situations too such as in MS Word and Windows desktop searches!

Where we want to refer to a value in a cell and put a wildcard character before or after it we need to use the & character to combine the values. In the above example, if we wanted to refer to text containing the value in cell F4 (rather than hard-coding ‘Sales’) we could write this as “*”&F4&”*”.

Remember that any literal text strings (including wildcard characters) have to be inserted in speech marks so that Excel knows this isn’t a reference to a cell or named range.

In the Topic Example Workbook, in cells H3 and H4 we have two values we want to search for in the department names (column B) – ‘Sales’ and ‘Technology’. In Columns I to K we want to use the MATCH Function to lookup a department contain ending in, starting with and containing each of these values. Use the MATCH function combined with the “*” wildcard character and the Ampersand (&) character.

In Column L we want to find the quantity sold by all the departments containing ‘Sales’ and ‘Technology’ in the department name respectively using the SUMIF functions.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Performing a wildcard Find and Replace in MS Word

http://word.mvps.org/faqs/general/usingwildcards.htm

YouTube Video – Excel Magic Trick 561: Wildcards * and ?