A Better SEARCH Function for Excel

The SEARCH function has a couple of issues that make it difficult to use. This makes it a prime candidate for a custom function to fix its limitations.

The SEARCH function has two limitations.

  1. It returns an error of it doesn’t find what is being searched for.
  2. It doesn’t handle blank/empty cells correctly.

The SEARCH function returns a number representing the position of what is being searched for. The SEARCH function will “find” a blank cell and return the number 1.


In the table below we can see examples of these shortcomings in column C – the formula is shown in column D.

Note cell B8 is empty yet the SEARCH function returns a 1.

To get around the error issue we can use the IFERROR function (column E), but that still shows 1 for the empty cell B8.

The ultimate solution is in column G. You can see we have now used three separate functions to return the position number of the entry in column B in column A.

Custom function

To simplify the SEARCH calculation and return 0 instead of an error we can create a custom function to replace the SEARCH function.

The LAMBDA accepts two inputs find-txt and in_txt.

If find_txt is blank then 0 is returned. If an error is encountered (any error) then 0 is returned. Otherwise the SEARCH function result is returned.

The custom function is shown being used in column E above, and its definition is shown in the image below.

To see the LAMBDA laid out differently see the image below which uses line breaks to split up the LAMBDA.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

7 thoughts on “A Better SEARCH Function for Excel

  1. Here is another way to write your LAMBDA (it is not a better way, just a different way)…


  2. Here is a modification to your lambda which adds an optional third argument named ignore_case. If omitted or if TRUE (or 1) is passed into it, the function will use the SEARCH for the search; otherwise, if you pass in FALSE (0r 0), the the FIND function will be used for the search…


    • Hmm! On my computer, the formula I just posted runs out of the comment box and into the ads on the right. Here is the formula with deliberate line breaks to ensure it fits the comment box properly….


      • Hi Rick
        Thanks for taking the time to add in ignore case option tweak with the FIND. Two searches in one!

        This one looks better – as you say the previous formula runs over to the right.