As always, another Excel formula question

Factual Questions
1

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.

2

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.

3

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:


=IF(ISNA(VLOOKUP(A3,'Other Worksheet Name'!$B$3:$C$62,2,FALSE)),"",VLOOKUP(A3,'Other Worksheet Name'!$B$3:$C$62,2,FALSE))
4

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?

5

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.

6

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.