The Find function in Excel is a text function that returns the position of a set character on a cell item. As the function is tricky to use, many new Excel users experience issues in running the Find function.
The Find function is case-sensitive in nature. This means you need to match the case of the character you want the function to return the value for. If you do not match the case, you might encounter the #VALUE! Error. There are other rookie errors as such that can create problems when using the Find function.
Use Proper Syntax
If you do not use proper syntax while entering the FIND function, you will run into the #NAME? error code. The formula for the FIND function is written in the following format:
=FIND(find_text, within_text, [start-num])
You can either refer to a cell or enter a text in place of “find_text”. If you’ve entered text, be sure to enter it inside double quotes. Similarly, be sure to use commas to separate your arguments.
In this example, we set the find_text value as I. As we did not enclose it inside double quotes, Excel triggered the #NAME? error.
To solve this, enter your text in double-quotes. The FIND function returns 1 as the letter “I” appears only once in “Image”.
Recheck Your Formula
You could have entered an incorrect value in the find_text section. If Excel cannot find the value you’ve requested in the set cell location, it will return the #VALUE! error.
Check your formula to see if you’ve correctly entered your values. If you’ve used a different case in your formula, change it to match the case.
We’ve entered the find_text value as “A” in the grid above. The FIND function will look for this character in A2, which is “apple”. As the case for the letter “a” is different, the FIND function will not work.
You can solve this error by changing the case for find_text to “a”.
Change start-num Value
You can add a start-num value at the end of your FIND formula. This specifies a position in the character Excel can start looking for your set value. If you’ve set a start-num value that comes after your set character, Excel will not register that character.
For example, we’ve entered our formula to find the number of times the letter “a” has been repeated in apple. However, we’ve set the start-num value to 2, which comes after “a” in the value. Excel cannot locate “a” in the remaining text, so it returns the #VALUE! error.
To fix this, we can change the start-num value to 1. However, you can choose not to enter 1 at all as Excel sets the start-num value to 1 by default.
Use Find Command in Excel
If you’re having trouble using the FIND function in Excel, you can always find the Find command. You can configure the Find command to Match case and Match entire cell contents with the value you enter.
Similarly, you can adjust where Excel looks for your set value. You can run the Find command within your sheet or the entire workbook. You can also choose if the command searches by rows or columns in formulas, values, or comments.
- On your workbook, hit Ctrl + F.
- Click on the Options>> button.
- Enter your value in the Find what section and configure the search settings accordingly.
- Select Find All.
The Find command will locate all cells that match your set value.
Repair Office
You could be dealing with corrupt or missing program files if non of the solutions apply. You can perform an online repair on the Office app as your last resort to the Find function not working in Excel.
- Open Settings (Windows + I).
- Head to Apps > Apps & features.
- Enter Microsoft Office in the search bar.
- Click the three-dot menu and select Modify.
- Choose Online repair > Repair.
