Another MS Excel question

Factual Questions
1

OK, I’m using the VLOOKUP function, and I have one little problem. When it can’t find the name its looking up, is there a way for it to return the value 0 instead of #N/A?

What I’m doing is using the function to lookup baseball statistics. I have one sheet in my workbook with statistics for every player in the league in 2000. On another sheet I have it set up so I type in a player’s name in one cell, and it will return statistics in the next few cells in the row. If I type in a player’s name who didn’t play in the majors in 2000, and therefore isn’t on the statistics list, it returns the #N/A for all of his stats. Instead of returning #N/A, I just want it to return a zero value.

For example, if I type in “Martinez, Pedroj” on the second sheet, everything works fine and it returns his 2000 pitching stats. However, if I type in “Sheets, Ben” it returns a bunch of #N/A’s and that in turn screws up my totals. Any way to overcome this problem?

2

Couldn’t you combine an IF statement with an ISVALID evaluation? I’m not an Excel maven but I’d think you could do something like

If(ISVALID(VLOOKUP(whatever)), VLOOKUP(whaver), 0)

3

Hmm, my FileMaker roots are showing. In Excel, that’s ISNA, not ISVALID, and it works in reverse from FileMaker’s IsValid function:

If(ISNA(VLOOKUP(whatever)), 0, VLOOKUP(whatever))

4

This “looks” awkward (repeating the VLOOKUP formula twice), but I think it’s the standard way of handling this (see the examples in the HELP for the ISERROR command, and they do something similar). I might wish that Microsoft would add a “default” for “not found” other than the ugly #NA, but I guess that won’t happen anytime soon.

(ignore line feeds)

=IF(ISERROR(VLOOKUP(<lookupvalue>,<tablerange>,<tablecolumn>,FALSE)),0,VLOOKUP(<lookupvalue>,<tablerange>,<tablecolumn>,FALSE)

(you could also put “” in place of 0 if you wanted it to be blank, or any other value such as “Not Found”)

It uses the
IF(ISERROR(<formula>),<result if error>,<result if not error>)
function.

Hope that helps.

5

Here’s another option to consider:

Excel 97 and beyond have conditional formatting, where you can have a cell change color or fonts or whatever based on it’s content. You can use that feature to solve your problem.

Go to the cell where you want to get rid of the error message.
Chose Format menu. Choose Conditional Formatting.
Change Condition 1 to “Formula Is”.
In the edit box, enter this formula (pretend the cell you are formatting is A1)

Click the Format button. Click the color dropdown. Click the white square.
Click OK. Click OK.

RexKatWA wrote

To accomplish the equivalent of this, you can use the technique I described. A simple way to spread the format across the entire spreadsheet is to set up A1 as I described above. Then click the Format Painter button (yellow paintbrush thing), then press control-A (select all). Now any errors that come up on this sheet will display blank. You may be able to save this blank spreadsheet as your default, but I’ve never tried.