I have this
LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212)
Which works.
But if I want to make it so that when e5 is empty I don’t get #N/A I wrapped it in an if statement to check for an empty cell and, if empty set the value to “” and if not empty run the lookup.
=IF(E5 = null,"", LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212))
But it fails. It just gives #NAME? for everything.
Am I doing it wrong?
Try:
=IF(E5 =#NULL!,"", LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212))
Or:
=IF(ISBLANK(E5),"", LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212))
This gives me #NULL!
This gives me #N/A
ETA: My mistake. I’d renamed the lookup sheet. the latter works… the former doesn’t.
Thanks for the help 
FYI I’ve evolved it a bit. (because the lookup function picks a ‘default’ value if the one entered isn’t in the list)
=IF(COUNTIF(hiddensheet!A$2:$A$212,E5) = 0,"", LOOKUP(E5,hiddensheet!A$2:$A$212,hiddensheet!$B$2:$B$212))
It only does that if you omit the final optional argument, which defaults to TRUE. If TRUE, then if there is no exact match it finds the next largest value that is less than lookup_value. TRUE will have the desired behavior only if the lookup column is sorted in ascending order. If you provide this argument with a value of FALSE, then you will always get an exact match. If there is not exact match, it will return #N/A.
ETA: I spoke too soon. My description is for VLOOKUP. I always use HLOOKUP or VLOOKUP and have not used LOOKUP. My apologies.
There is no reserved word #NULL! that can be used in a formula. In a formula you have to use a function to test for special values. If a cell contains the error #NULL! then the function ISERROR will return TRUE (it will also return TRUE for other errors).
However, the #NULL! error is not related to the OP. It occurs when you specify an intersection of two areas that do not intersect.
ISBLANK is a correct test for a blank cell. The test
=""
also works.
I’ve found earlier versions of Excel did not like to reference other sheets. By this I mean if I typed in a formula and it was wrong Excel would not accept a correct formula. I would have to copy an unused cell in order to fix the problem and then type in the formula correctly the first time.
you can make a lookup statement return an exact match by adding a zero to the formula. example: =vlookup(c5,a1:b20,2,0) The zero means it will only return results from column 2 if it finds a match for what is in cell c5.
Sounds like you are using 2003.
Just use your original formula but like this:
=IF(len(E5) = 0,"", LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212))
If there are no characters in E5 it will do what you want.
I’ve always used ="" as mentioned by CookingWithGas, it requires fewer characters.
=IF(E5="","",LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212))
Or you could use =IF(ISNA (E5),"",…)
To test for a blank? No, ISNA only tests to see if the content is #N/A!