add ISNA function to working INDEX/MATCH formula

[Guest]

Hello,
I am unisg the following formula which works fine but I need to add error
correction to change cells that display #N/A to cells that are blank. I can't
seem to get the right number of commas, brackets and whatnot in the right
places to make it work...

=INDEX('[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$100,MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE),MATCH($N$1,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$1,FALSE))

thanks,
Robert

 

[Max]

Since there are 2 MATCH() involved,
the structure would be something like..:
= IF(OR(ISNA(MATCH(1st)),ISNA(MATCH(2nd))),INDEX())

Try this (untested) .. :

=IF(OR(ISNA(MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE)),ISNA(MATCH($N$1,'[Payroll ERO WK
99
2004.xls]Technicians'!$A$1:$AN$1,FALSE))),"",INDEX('[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$100,MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE),MATCH($N$1,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$1,FALSE)))

 

[Biff]

Don't you just love those big ugly file names!
And note that the entire path is not included!

Although error trapping is highly desirable, when you get
those formulas that grow in length due to long file names
and therefor render the formula difficult to read an
alternative might be to use conditional formatting to hide
the #N/A.

Conditional formatting
Formula is: =ISNA(A1) or =ISERROR(A1)
Set the text color to be the same as the background color.

Of course, you'll have to take into consideration if this
approach might cause some problem in a downstream
calculation due to the #N/A.

Biff

-----Original Message-----
Since there are 2 MATCH() involved,
the structure would be something like..:
= IF(OR(ISNA(MATCH(1st)),ISNA(MATCH(2nd))),INDEX())

Try this (untested) .. :

=IF(OR(ISNA(MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE)),ISNA(MATCH ($N$1,'[Payroll ERO WK
99
2004.xls]Technicians'!$A$1:$AN$1,FALSE))),"",INDEX ('[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$100,MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE),MATCH ($N$1,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$1,FALSE)))

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hello,
I am unisg the following formula which works fine

but I need to add
error

correction to change cells that display #N/A to cells

that are blank. I
can't

seem to get the right number of commas, brackets and whatnot in the right
places to make it work...

=INDEX('[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$100,MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE),MATCH ($N$1,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$1,FALSE))

thanks,
Robert

.

 

[Guest]

Thanks for the responses!
The following formula works fine:
=IF(ISNA(MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE)),"",INDEX('[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$100,MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE),MATCH($N$1,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$1,FALSE)))

As you see, I only actually need error handling on the first match. and I
would prefer to see error messages if there are any other problems.
Therefore, although the conditional formatting is a good idea that I will
keep in mind, in this particular case, I will stick with the long formula.

R.

Don't you just love those big ugly file names!
And note that the entire path is not included!

Although error trapping is highly desirable, when you get
those formulas that grow in length due to long file names
and therefor render the formula difficult to read an
alternative might be to use conditional formatting to hide
the #N/A.

Conditional formatting
Formula is: =ISNA(A1) or =ISERROR(A1)
Set the text color to be the same as the background color.

Of course, you'll have to take into consideration if this
approach might cause some problem in a downstream
calculation due to the #N/A.

Biff

-----Original Message-----
Since there are 2 MATCH() involved,
the structure would be something like..:
= IF(OR(ISNA(MATCH(1st)),ISNA(MATCH(2nd))),INDEX())

Try this (untested) .. :

=IF(OR(ISNA(MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE)),ISNA(MATCH ($N$1,'[Payroll ERO WK
99
2004.xls]Technicians'!$A$1:$AN$1,FALSE))),"",INDEX ('[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$100,MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE),MATCH ($N$1,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$1,FALSE)))

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hello,
I am unisg the following formula which works fine

but I need to add
error

correction to change cells that display #N/A to cells

that are blank. I
can't

seem to get the right number of commas, brackets and whatnot in the right
places to make it work...

=INDEX('[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$100,MATCH(A5,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$A$100,FALSE),MATCH ($N$1,'[Payroll ERO WK 99
2004.xls]Technicians'!$A$1:$AN$1,FALSE))

thanks,
Robert

.

 

[Max]

Thanks for the responses!

You're welcome, Robert, and glad to hear you got it working !

As a closure, thought I'll just correct some typos
in this line of my earlier post ..:

= IF(OR(ISNA(MATCH(1st)),ISNA(MATCH(2nd))),INDEX())

should have read as:

= IF(OR(ISNA(MATCH(1st)),ISNA(MATCH(2nd))),"",INDEX())

(missed out the comma and the blank "" before INDEX())