# can't run functions inside if statements in excel? **URL:** \ **Category:** Factual Questions\ **Created:** [May 8, 2009, 8:13pm UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782 "2009-05-08T20:13:38Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [May 8, 2009, 8:13pm UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/1 "2009-05-08T20:13:38Z")
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? ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [May 8, 2009, 8:22pm UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/2 "2009-05-08T20:22:55Z")
Try: =IF(E5 =#NULL!,"", LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212)) ---
**Author:** ![JeffB](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jeffb/32/188_2.png) [@JeffB](https://boards.straightdope.com/u/JeffB)\ **Post date:** [May 8, 2009, 8:28pm UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/3 "2009-05-08T20:28:26Z")
Or: =IF(ISBLANK(E5),"", LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212)) ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [May 8, 2009, 8:45pm UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/4 "2009-05-08T20:45:18Z")
> [@Giles](#): > > Try: > > =IF(E5 =#NULL!,“”, LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212)) This gives me #NULL! > [@JeffB](#): > > Or: > > =IF(ISBLANK(E5),“”, LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212)) 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 🙂 ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [May 8, 2009, 9:38pm UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/5 "2009-05-08T21:38:35Z")
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)) ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [May 9, 2009, 2:33am UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/6 "2009-05-09T02:33:37Z")
> [@Lobsang](#): > > …the lookup function picks a ‘default’ value if the one entered isn’t in the list… 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. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [May 9, 2009, 2:40am UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/7 "2009-05-09T02:40:38Z")
> [@Giles](#): > > Try: > > =IF(E5 =#NULL!,“”, LOOKUP(E5,Sheet2!A$2:$A$212,Sheet2!$B$2:$B$212)) 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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [May 9, 2009, 3:10am UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/8 "2009-05-09T03:10:20Z")
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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [May 9, 2009, 3:14am UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/9 "2009-05-09T03:14:32Z")
> [@Lobsang](#): > > 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)) 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. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [May 9, 2009, 3:26am UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/10 "2009-05-09T03:26:53Z")
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. ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [May 9, 2009, 3:40am UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/11 "2009-05-09T03:40:25Z")
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)) ---
**Author:** ![Quartz](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/quartz/32/267_2.png) [@Quartz](https://boards.straightdope.com/u/Quartz)\ **Post date:** [May 9, 2009, 3:13pm UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/12 "2009-05-09T15:13:32Z")
Or you could use =IF(ISNA (E5),"",…) ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [May 9, 2009, 7:45pm UTC](https://boards.straightdope.com/t/cant-run-functions-inside-if-statements-in-excel/495782/13 "2009-05-09T19:45:05Z")
> [@Quartz](#): > > Or you could use =IF(ISNA (E5),“”,…) To test for a blank? No, ISNA only tests to see if the content is #N/A!