removing na

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
=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...
 
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
 
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")
 
Back
Top