Excel Search Tips Wildcards, Functions, and More
Original title: 'The boss asked me to find all customers with the surname Hu, and I used a * sign and finished it in 3 seconds!'
Hello everyone! I'm Satellite Sauce~
In general, when we process data, as long as we use the right formula, we can solve most of the search problems.
But if we encounter non-standard data, such as incomplete information, no exact matching items, or containing hidden characters... This is very difficult to handle...
Don't want to spend time cleaning the data. Is there a way to directly get the part I want from these 'dirty' data?
If you want to know, keep reading~
1. Wildcards
In Excel, '?' represents any single character, and '*' represents any number of characters.
The boss wants us to find a customer, but only knows that his surname is 'Hu' and there is a character 'Ze' in the name.
Just press 【Ctrl+F】to search, enter 'Hu*Ze', and search all:
After selecting all and filling with a color, all the names that meet the requirements will be found out~
2. Functions
Some functions themselves have the function of fuzzy search, such as the lookup series, and can choose 'exact' or 'approximate' matching by modifying parameters.
Generally used in the case where the search value is not a definite numerical value, but some dividing lines.
For example, dividing the performance level of employees according to the production volume:
=VLOOKUP(B2$A$8$B$11,2,TRUE)
(Note that the search area must be sorted from small to large, here it refers to A8:B11.)
3. Comprehensive Application
Why did it go wrong when searching for products according to the salesman?
It turns out that there are invisible characters in column B.
Change the formula:
=VLOOKUP(*&F2&*B2:C6,2,FALSE)
It can prevent being backstabbed by the invisible content~
4. Written at the End
Okay, that's it for today's sharing!
In fact, in addition to wildcards and functions, as well as their combined use, there are other fuzzy search techniques. Do you guys know what they are?
This article is from the WeChat public account: Qiuye Excel (ID: excel100), author: Satellite Sauce