IF(FIND Formula gives #VALUE! error



I have a brick selection sheet with Data Validations in Lists of Brick Blends.
I have added * to the end of each brick blend type to signify the amount of
different bricks ie. Colonial Blend** means 2 x different bricks required
(Non Chargeable to client) and Homestead Blend*** means 3 x Different Bricks
required. (Chargeable to client)

I have tried to write a formula that puts an X in a cell adjacent to the
Data Validation cell (means Chargeable to client) for 3 brick blend and if it
is anything else leave the cell blank. I keep getting the #VALUE! Error.

For Example:- =IF(FIND("***",J19,1)>0,"X","")
It works fine putting the "X" value in the adjacent cell, but when I want it
to return a value of nothing ("") it gives me the error.


One possible fix:



ther werre two things wrong. first the * ws being treated as a wild carrd
character not a real *. Need to switch to Search to handle the *. the ~
tells search tto look for the * (not a wild carrd). Second problem if ***
wasn't found a value error is returned from both find and search. had to add
ISNUMBER to eliminate the error that occured when *** was not found.


Dave Peterson

I don't think you want the tilde character if you use =Find(). But when you
switched to =search(), it was required.

(I thought it was interesting.)


Thanks Dave, Much appreciated.
I'm just an amateur at this, and guys like you are worth their weight in gold.
The girls at work will be most impressed when I tell them "I" figured it out
Thanks again mate.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question