Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

This topic provides help for the most common scenarios for the #VALUE! error in the FIND/FINDB and SEARCH/SEARCHB functions.

A few things to know about FIND and SEARCH functions

  • The FIND and SEARCH functions are very similar. They both work in the same way - locate a character or a text string in another text string. The difference between these two functions is that FIND is case-sensitive, and SEARCH is not case-sensitive. So if you don’t want to match case in a text string, use SEARCH.

  • If you want a function that returns the string based on the character number you specify, use the MID function along with FIND. You can find information and examples of using MID and FIND combinations in the FIND help topic.

  • The syntax of these functions is the same, find_text, within_text, [start_num]). In simple English, the syntax means What do you want to find?, Where do you want to find it?, What position do you want to start from?

Problem: the value in the find_text argument cannot be found in the within_text string

If the function cannot find the text to be found in the specified text string, it will throw a #VALUE! error.

For example, a function like:

  • =FIND("gloves","Gloves (Youth)",1)

Will throw the #VALUE! error, because there is no matching “gloves” in the string, but there is “Gloves”. Remember that FIND is case-sensitive, so make sure the value in find_text has an exact match in the string in the within_text argument.

However, this SEARCH function will return a value of 1, since it’s not case-sensitive:

  • =SEARCH("gloves","Gloves (Youth)",1)

Solution: Correct the syntax as necessary.

Problem: The start_num argument is set to zero (0)

The start_num argument is an optional argument, and if you omit it, the default value will be assumed to be 1. However, if the argument is present in the syntax and the value is set to 0, you will see the #VALUE! error.

Solution: Remove the start_num argument if it is not required, or set it to the correct appropriate value.

Problem: The start_num argument is greater than the within_text argument

For example, the function:

  • =FIND(“s”,”Functions and formulas”,25)

Looks for “s” in the “Functions and formulas” string (within_text) starting at the 25th character (start_num), but returns a #VALUE! error because there are only 22 characters in the string.

Tip: To find the total number of characters in a text string, use the LEN function

Solution: Correct the starting number as necessary.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Correct a #VALUE! error

FIND/FINDB functions

SEARCH/SEARCHB FUNCTIONS

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×