Formula always returns False instead of value, Why?

Why does serialCell always return False? I would expect it to return the value of the cell in column L next to matched cell in column K

Please Login or Register  to view this content.

Why is it returning FALSE? Follow the logic you have:

Please Login or Register  to view this content.

1) It checks to see if serialCell is Empty. If it is, then
2) let Serialcell (.value implied) become the value of the rest of this expression. This expression is a boolean expression that checks the Serialcell.formula string and compares to that text string in the statement. We already established that serialcell is empty, so serialcell.formula cannot be the same as that text string, so the result of the boolean expression is FALSE, so VBA assigns that value to the serialcell.Value property (and triggers a new calculate event).

If you want to return the result of the lookup function, then you need to "translate" that Excel formula into VBA syntax. This is not the kind of programming I do, so I am not sure exactly what you need to do here. With the data validation in place, is it necessary to test if the lookup will fail? If not, this might be as simple as

Please Login or Register  to view this content.

If that doesn't work, the idea is still that you need to think through the logic you are trying to use with that formula string and translate that into a statement (or statements) that VBA can use to recreate that logic.