When you are dealing with loads of data entries, and you want to find something in particular but you do not remember the whole thing, here’s what you can do:
As you can see in the image above, we have 3 sections with different data, and we want to search “dishkiau” from the same.
To search the same, we will be using INDEX Function and MATCH Function.
Previously: How to get the domain from email address?
So, the first thing that we will do is add the INDEX Function:
The following formula includes array, row number, column number (optional).
Now we will select the array which is B3:D3 for the above example.
Then skipping the row number, we will input the MATCH Function.
The same requires lookup value, lookup array, and match type (optional).
The lookup value is cell E3 i.e. the search we want to make – dishkiau.
Including the search we will input &”*”.
Then we will come to the point of match type. As you can see in the image above, we have three options, less than, exact match and greater than.
So, for the example above we will select the option:
(…)0 – Exact match
Then we will close the MATCH Function and then we will close the INDEX Function.
So overall, the formula becomes:
Now all we have to do is press enter.
And BAAAAMMM!!! The answer is right on your screen, i.e. dishkiau99.
Now, all we have to do is drag the formula down to search other values as well from the available data in the sheet.
It’s easy, isn’t it?
Learn more:- How to group by numbers using VLOOKUP formula?