ISERROR add on

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

i need to add an error statement to this so when it can't find the value it
wount put an N/A in the cell. need it to put 0 in..

Excel xp
windows xp pro

=VLOOKUP(B12,$B$87:$T$697,19,FALSE)

thank you.
 
On way:

=IF(ISNA(MATCH(B12, $B$87:$T$697, FALSE)), "",
VLOOKUP(B12,$B$87:$T$697,19,FALSE)
 
J.E. McGimpsey said:
On way:

=IF(ISNA(MATCH(B12, $B$87:$T$697, FALSE)), "",
VLOOKUP(B12,$B$87:$T$697,19,FALSE)
Click to expand...
....

Maybe this works differently in more recent versions, but

MATCH(B12, $B$87:$T$697, FALSE)

invariably returns #N/A in Excel 97 and 2000 because the 2nd argument to
MATCH in those versions must be either single column or single row
regardless of what online help states.

=IF(SUMPRODUCT(-(B12=$B$87:$B$697)),VLOOKUP(B12,$B$87:$T$697,19,0),"")

would be an alternative.
 
Harlan Grove said:
Maybe this works differently in more recent versions, but
Click to expand...

Nope, it works differently in situations where I type one thing and
test another. Thanks for the correction.
 
Hi

Another one:

=IF(ISERROR(VLOOKUP(B12,$B$87:$T$697,19,FALSE)),0,VLOOKUP(B12,$B$87:$T$697,1
9,FALSE))
or with $B$87:$T$697 defined as named range MyRng somewhat shorter version
of it:
=IF(ISERROR(VLOOKUP(B12,MyRng,19,FALSE)),0,VLOOKUP(B12,MyRng,19,FALSE))


Arvi Laanemets
 
Back
Top