At the moment, when I am using VLOOKUP to extract data from another worksheet, when it is extracting a blank cell, instead of displaying it as empty on the local sheet, it displays a "0" in the cell. Here's my formula. I've tried many combos, but have given up repeatedly trying. Thanks.
Formula:
Please Login or Register to view this content.
What's in B21 and what's in column L for a matching B21 value?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
If a successful lookup only finds an empty cell, then it will return 0. Even =A1 will return 0 if A1 is empty.
There are several approaches to handle this, for example format the cells not to display zero values with custom format
0.00,-0.00;;
or similar, depending on the format you need.
Or you wrap your formula in an IF statement that checks if the returned cell is empty
=if(YourFormula="","",YourFormula)
Or, in your specific case, since you already use an IF statement, add an OR() to the condition
Please Login or Register to view this content.
Last edited by npamcpp; 06-30-2012 at 07:22 PM.
Like a post? Click the star below it!
That works fine, however, it is displaying "N/A" in the cells where there are no records. How can I remove these N/As so the cells remain blank?Originally Posted by npamcpp
If a successful lookup only finds an empty cell, then it will return 0. Even =A1 will return 0 if A1 is empty.
There are several approaches to handle this, for example format the cells not to display zero values with custom format
0.00,-0.00;;
or similar, depending on the format you need.
Or you wrap your formula in an IF statement that checks if the returned cell is empty
=if(YourFormula="","",YourFormula)
Or, in your specific case, since you already use an IF statement, add an OR() to the condition
Please Login or Register to view this content.
Try:
Formula:Please Login or Register to view this content.
This will put blanks where no records are found.
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Yes, but I am still getting error messages, probably not putting a parenthesis in the right place or something.Try:
Formula:Please Login or Register to view this content.
This will put blanks where no records are found.
Formula:Please Login or Register to view this content.
Probably time to upload your workbook.
When you get a moment would you also extend your profile to indicate which Excel versions you have. Knowing this can be useful since later versions of Excel have functionality that does not exist in earlier versions
If the first formula worked fine then this should work also:
Formula:Please Login or Register to view this content.
The closing bracket for the ISNA() is missing. It should beTry:
Formula:Please Login or Register to view this content.
This will put blanks where no records are found.
Formula:Please Login or Register to view this content.
ACM2, your question is about avoiding the return of Zero from a formula. A zero as the result is not an error and can be handled by the techniques described above. If the formula returns #N/A, then it's an error and needs to be handled differently. You can either use a formula like the one above, or in Excel 2007 and later use
Formula:Please Login or Register to view this content.
or you can prevent errors occurring in the first place by good data management. E.g. ensure that lookup value and the data in the lookup range have the same data type, no leading or trailing blanks, and more.
As has been mentioned above, this would be easier to demonstrate with a workbook.
Here is a good article on why Vlookup() can fail: Six Reasons Why Your VLOOKUP or HLOOKUP Formula Does Not Work
Last edited by npamcpp; 07-02-2012 at 07:12 AM.
That worked fine. Thanks.If the first formula worked fine then this should work also:
Formula:Please Login or Register to view this content.
If avoiding both #N/A and 0 as the result is the goal, then this is a shorter way:If the first formula worked fine then this should work also:
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
As suggested earlier, it's also a good idea to have your excel version indicated in your profile. Also a brief description of your sheet helps, more information leads to more accurate answers.
One possible answer to your question would be
=T(IFERROR(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0),""))
but that would be of no use if you use excel 2003, or if the lookup results are numeric.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks