ISNA help needed

[cashmire]

Hi,

anyone please help with a ISNA formula I'm trying to create. The following formula creates an #N/A result:
=VLOOKUP(D19475&AW19475,'SGD Exch'!$J:$K,2,FALSE)

I'd like to blnk out this result. If I run an ISNA on the vlookup formula is just says TRUE but I need it to blank it so I can use the data in a pivot table.

Anyone have an idea on how to solve this?

thanks!

 

[T. Valko]

Hi,

anyone please help with a ISNA formula I'm trying to create. The following formula creates an #N/A result:
=VLOOKUP(D19475&AW19475,'SGD Exch'!$J:$K,2,FALSE)

I'd like to blnk out this result. If I run an ISNA on the vlookup formula is just says TRUE but I need it to blank it so I can use the data in a pivot table.

Anyone have an idea on how to solve this?

thanks!

Try one of these...

This one will work in ANY version of Excel:

=IF(ISNA(VLOOKUP(D19475&AW19475,'SGD Exch'!$J:$K,2,0)),"",VLOOKUP(D19475&AW19475,'SGD Exch'!$J:$K,2,0))

This one will work in Excel 2007 or later:

=IFERROR(VLOOKUP(D19475&AW19475,'SGD Exch'!$J:$K,2,0),"")

Note that the IFERROR version will trap ALL errors, not just #N/A errors.

 

[cashmire]

Valko,

the iferror formula is brilliant! It solves all my problems.

thanks!

Peter

 

[T. Valko]

Valko,

the iferror formula is brilliant! It solves all my problems.

thanks!

Peter

You're welcome. Thanks for the feedback!