Excel Question - error.type

Factual Questions
1

Good-day Dopers!

I am helping a coworker with an Excel spreadsheet and have a catch 22 that I cannot find a work around for.

BTW, we are working in Excel 97 SR-2.

The problem I have is with a VLOOKUP result. If the value the function is looking for is not in the table, it returns an #N/A. If the data were not there, I would prefer to display a blank cell. Therefore I wrote the formula:

=IF(ERROR.TYPE(VLOOKUP(criteria,table,index,false))=7,””,VLOOKUP(criteria,table,index,false))

This works great if the criteria data is not in the look-up range.

The catch 22 is that if the criteria is in the look-up range. The ERROR.TYPE function returns an #N/A since the result of the VLOOKUP is no longer an error. This bombs out the IF-THEN statement and puts a #N/A in the cell.

So if I use only the VLOOKUP statement, I get an #N/A if no items in the table match my criteria. If I use the IF-THEN statement, I get an #N/A if there is an item in the table that matched the criteria. Either way I am stuck with an #N/A in one of the conditions if the data is/is not in the table.

Oddly enough, the above formula is exactly what Excel help says to do. Too bad it does not work.

Does anyone have any ideas whatsoever how to get around this?

Much appreciation…

2

Bean,

I think this feature is in 97. What about Conditional Formatting. If the formula value is “#N/A” have it turn the type white or whatever color of the backgound is.

What do you think?
Jim

3

=IF(ISERROR(VLOOKUP(criteria,table,index,false)),"",VLOOKUP(criteria,table,index,false))

Your problem is that you’re telling Excel to ASSUME there’s an error, and then determine what type it is. You want to instead ask it IF there’s an error.

4

Jpeg Jones, Thank you! That worked perfectly.

Damn, that was so simple I’m embarrased. I’m going to go hide under my rock now…