# Need help with index match functions...inside..

Posted by NiuB on July 09, 2001 10:02 AM

Hello..

I'm using this formula :

=INDEX('Data Buat Sijl Murid'!\$B\$14:\$AO\$83,MATCH(\$H\$14,'Data Buat Sijl Murid'!\$B\$14:\$B\$83,0),29)

the problem is when there is no data in return on the spesific cells,
it will shows #N/A. How can I hide this #N/A from show in my file
and also will be hide when I print the data..Many thanks

Posted by Barrie Davidson on July 09, 2001 10:07 AM

=IF(ISNA(INDEX('Data Buat Sijl Murid'!\$B\$14:\$AO\$83,MATCH(\$H\$14,'Data Buat Sijl Murid'!\$B\$14:\$B\$83,0),29)),"",INDEX('Data Buat Sijl Murid'!\$B\$14:\$AO\$83,MATCH(\$H\$14,'Data Buat Sijl Murid'!\$B\$14:\$B\$83,0),29))

This will leave the cell blank ("") if the formula's result is #N/A.

Regards,
Barrie

Posted by Aladin Akyurek on July 09, 2001 10:31 AM

NiuB,

You get #N/A not because of "when there is no data in return on the spesific cells" as you put. It's a value that MATCH returns because it failed to find the "lookup value" \$H\$14 exactly as is in 'Data Buat Sijl Murid'!\$B\$14:\$B\$83. [0 means False means exact match ]. Anycase, INDEX has nothing to do with the "so-called" error value. See also allexperts.com for an alternative formulation with the same intent as Barrie's suggestion.