removing na

[Steve]

hi,

how do i remove the #N/A results from this formula?

=INDEX('c:\[Reporting
10-01-12.xls]Sheet2'!$I$2:$I$1000,MATCH($H$1,'c:\[Reporting
10-01-12.xls]Sheet2'!$B$2:$B$1000,0))

thank you for your help

 

[Jim Thomlinson]

=if(isna(MATCH($H$1,'c:\[Reporting 10-01-12.xls]Sheet2'!$B$2:$B$1000,0)), "",
INDEX('c:\[Reporting 10-01-12.xls]Sheet2'!$I$2:$I$1000,MATCH
$H$1,'c:\[Reporting 10-01-12.xls]Sheet2'!$B$2:$B$1000,0)))

Or something like that...

 

[Pete_UK]

Try this:

=IF(ISNA(MATCH($H$1,'c:\[Reporting 10-01-12.xls]Sheet2'!$B$2:$B
$1000,0)),"", INDEX('c:\[Reporting 10-01-12.xls]Sheet2'!$I$2:$I
$1000,MATCH($H$1,'c:\[Reporting 10-01-12.xls]Sheet2'!$B$2:$B$1000,0)))

You can change the "" in the middle for some message if you like.

Hope this helps.

Pete

 

[Bob Umlas]

If Excel 2007:
=IFERROR(INDEX('c:\[Reporting
10-01-12.xls]Sheet2'!$I$2:$I$1000,MATCH($H$1,'c:\[Reporting
10-01-12.xls]Sheet2'!$B$2:$B$1000,0)),"NOT FOUND")