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:

Example:

value of the first matc

As you can see in the image above, we have 3 sections with different data, and we want to search “dishkiau” from the same.

value of the first matc

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:

=INDEX(

The following formula includes array, row number, column number (optional).

value of the first matc

Now we will select the array which is B3:D3 for the above example.

value of the first match

Then skipping the row number, we will input the MATCH Function.

=MATCH(

The same requires lookup value, lookup array, and match type (optional).

value of the first match

The lookup value is cell E3 i.e. the search we want to make – dishkiau.

value of the first match

Including the search we will input &”*”.

value of the first match

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

value of the first match

Then we will close the MATCH Function and then we will close the INDEX Function.

So overall, the formula becomes:

=INDEX(B3:D3,MATCH(E3&”*”,B3:D3,0))

value of the first match

Now all we have to do is press enter.

And BAAAAMMM!!! The answer is right on your screen, i.e. dishkiau99.

value of the first match

Now, all we have to do is drag the formula down to search other values as well from the available data in the sheet.

value of the first match

It’s easy, isn’t it?

Learn more:- How to group by numbers using VLOOKUP formula?