Solved

# Error #N/A problem

Posted on 2013-11-22
Medium Priority
154 Views
Folks,
The below code is OK. However if I have no value in cell B2 I get the error #N/A in the range A4:A12. I'm having a problem not showing that error with IF(ISERROR and I may not be doing that correctly, if it is what I need not to show the #N/A error which is my objective.
``````=IF(HLOOKUP(\$B\$2,\$J\$3:\$M\$12,ROW()-2,FALSE)=0,"",HLOOKUP(\$B\$2,\$J\$3:\$M\$12,ROW()-2,FALSE))
``````
0
Question by:Frank Freese
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 3

LVL 4

Expert Comment

ID: 39669321
for #N/A you should use ISNA instead of ISERROR
0

LVL 4

Accepted Solution

yuppydu earned 2000 total points
ID: 39669331
or you can simply add an IF(b2="";"";IF(HLOOKUP(\$B\$2,\$J\$3:\$M\$12,ROW()-2,FALSE)=0,"",HLOOKUP(\$B\$2,\$J\$3:\$M\$12,ROW()-2,FALSE)))
0

Author Comment

ID: 39669477
You suggestion works "almost" - if you will repost
IF(b2="";"";IF(HLOOKUP(\$B\$2,\$J\$3:\$M\$12,ROW()-2,FALSE)=0,"",HLOOKUP(\$B\$2,\$J\$3:\$M\$12,ROW()-2,FALSE)))
to
IF(b2="","",IF(HLOOKUP(\$B\$2,\$J\$3:\$M\$12,ROW()-2,FALSE)=0,"",HLOOKUP(\$B\$2,\$J\$3:\$M\$12,ROW()-2,FALSE)))

without the ; I can award you points. I do not want to post an incorrect solution.
0

Author Comment

ID: 39669935
yuppydu,
Please repost with changes so I can award you points.
Thanks you
0

LVL 4

Expert Comment

ID: 39669957
Sorry ; or , is just an argument separator. I didn't notice your system takes , therefore substitute ; with ,
0

Author Closing Comment

ID: 39670004
thank you
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month15 days, 2 hours left to enroll

#### 649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.