# As always, another Excel formula question **URL:** \ **Category:** Factual Questions\ **Created:** [March 25, 2009, 5:57pm UTC](https://boards.straightdope.com/t/as-always-another-excel-formula-question/490767 "2009-03-25T17:57:36Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [March 25, 2009, 5:57pm UTC](https://boards.straightdope.com/t/as-always-another-excel-formula-question/490767/1 "2009-03-25T17:57:36Z")
I have this **lookup** formula that works fine, but I want to hide the results if there is not an exact match and no value is returned. The formula is this =VLOOKUP(A3,‘Other Worksheet Name’!$B$3:$C$62,2,FALSE) It looks at a date in A3, compares it to a column of dates in column B of the other worksheet, and if it finds an exact match, returns a text string from the adjacent cell in column c of the other worksheet. With no exact match, the cell value displays as “#N/A”, and that’s what I want to hide. I’ve tried some things like putting the **vlookup** formula within a larger **if** formula, and making either the **then** or **else** parts of the statement as “”, but I’m getting nowhere. I’ve also tried several variations of conditional formatting, to make the font color white when that result appears, but no go. It appears that Excel sometimes seems to treat “#N/A” as an error message, and sometimes doesn’t. I found something in the help section that says it means “no matching value found”, which isn’t quite the same as an error. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 25, 2009, 6:24pm UTC](https://boards.straightdope.com/t/as-always-another-excel-formula-question/490767/2 "2009-03-25T18:24:43Z")
What about using a cell next to it that determines if it’s a non-“N/A” value and reports it, if not, reports back with " ". Then hide the original cell. ---
**Author:** ![mbetter](https://avatars.discourse-cdn.com/v4/letter/m/53a042/32.png) [@mbetter](https://boards.straightdope.com/u/mbetter)\ **Post date:** [March 25, 2009, 6:40pm UTC](https://boards.straightdope.com/t/as-always-another-excel-formula-question/490767/3 "2009-03-25T18:40:29Z")
There’s a specific worksheet function for this purpose, ISNA(). This function returns TRUE if the value specified is #N/A, false otherwise. So for your function, this should work: ```auto =IF(ISNA(VLOOKUP(A3,'Other Worksheet Name'!$B$3:$C$62,2,FALSE)),"",VLOOKUP(A3,'Other Worksheet Name'!$B$3:$C$62,2,FALSE)) ``` ---
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [March 25, 2009, 6:50pm UTC](https://boards.straightdope.com/t/as-always-another-excel-formula-question/490767/4 "2009-03-25T18:50:16Z")
If you’re suggesting treating “#N/A” as a text string value, that doesn’t work. Nor do the options about hiding cells with error messages. But maybe I’m misunderstanding. Let’s say I have that formula in B3, and C3 is unused. What should I put in C3? Or are you saying I should hide everything with that formula, and somehow in an adjoining array of cells, pull out everything that returns an exact match? In that case, can you suggest a formula? ---
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [March 25, 2009, 7:06pm UTC](https://boards.straightdope.com/t/as-always-another-excel-formula-question/490767/5 "2009-03-25T19:06:08Z")
> [@mbetter](#): > > There’s a specific worksheet function for this purpose, ISNA(). This function returns TRUE if the value specified is #N/A, false otherwise. > > So for your function, this should work: > > ```auto > > =IF(ISNA(VLOOKUP(A3,'Other Worksheet Name'!$B$3:$C$62,2,FALSE)),"",VLOOKUP(A3,'Other Worksheet Name'!$B$3:$C$62,2,FALSE)) > > ``` Whoo hoo, this works perfectly! I wish the help section was smart enough to offer commands like this as solutions when you make inquiries. Thanks so much. ---
**Author:** ![CCYMan](https://avatars.discourse-cdn.com/v4/letter/c/87869e/32.png) [@CCYMan](https://boards.straightdope.com/u/CCYMan)\ **Post date:** [March 25, 2009, 8:08pm UTC](https://boards.straightdope.com/t/as-always-another-excel-formula-question/490767/6 "2009-03-25T20:08:13Z")
There is also an “ISERROR” function that works like “ISNA,” but covers a broader range of things (#N/A, #VALUE!, and so on). I spend hours a day in Excel, and I love that function.