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

Try changing your formula to:
=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.

Aladin

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

Many thanks Barrie

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

Thanks to you too Aladin..you guys rock..many thanks for the response.