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…