Error Return Value from and INDEX(A:2,MATCH()) function

Hello,

I am using the following function =IF(INDEX('Page A'!F:F,MATCH('Page
B'!A9,'Mar A'!A:A,0))=#N/A,"",INDEX('Page A'!F:F,MATCH('Page B'!A9,'Page
A'!A:A,0))) and it keeps returning a #N/A when the MATCH() function errors
out, but what I want to show is "" (blank space). Obviously there is
something wrong with my formula but I can't figure it out. If anyone out
there can help me please let me know what I am doing wrong

Thanks!

Try using ISNA instead:
IF(ISNA(INDEX('Page A'!F:F,MATCH('Page B'!A9,'Mar
A'!A:A,0))),,"",INDEX('Page A'!F:F,MATCH('Page B'!A9,'Page
A'!A:A,0)))
That should get you a blank with a return of #N/A. Check out the other IS
functions, too (ISERROR, ISBLANK, ISREF, etc.).

Hope that helps.

Tom Hayakawa

"BJ" wrote:

Hello,

I am using the following function =IF(INDEX('Page A'!F:F,MATCH('Page
B'!A9,'Mar A'!A:A,0))=#N/A,"",INDEX('Page A'!F:F,MATCH('Page B'!A9,'Page
A'!A:A,0))) and it keeps returning a #N/A when the MATCH() function errors
out, but what I want to show is "" (blank space). Obviously there is
something wrong with my formula but I can't figure it out. If anyone out
there can help me please let me know what I am doing wrong

Thanks!

Try instead in this form:

=IF(ISNA(MATCH(...)),"",INDEX(.. MATCH(...)))

Error trap for the MATCH(...) part will do
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----
BJ wrote in message
...
Hello,

I am using the following function =IF(INDEX('Page A'!F:F,MATCH('Page
B'!A9,'Mar A'!A:A,0))=#N/A,"",INDEX('Page A'!F:F,MATCH('Page B'!A9,'Page
A'!A:A,0))) and it keeps returning a #N/A when the MATCH() function errors
out, but what I want to show is "" (blank space). Obviously there is
something wrong with my formula but I can't figure it out. If anyone out
there can help me please let me know what I am doing wrong

Thanks!

I am assuming you have a typo in the formula and Mar A should be Page A (or
if it is a translation that you missed?), however I am going to use Page A


=IF(ISNUMBER(MATCH('Page B'!A9,'Page A'!A:A,0)),INDEX('Page
A'!F:F,MATCH('Page B'!A9,'Page A'!A:A,0)),"")


should work


Regards,

Peo Sjoblom

"BJ" wrote:

Hello,

I am using the following function =IF(INDEX('Page A'!F:F,MATCH('Page
B'!A9,'Mar A'!A:A,0))=#N/A,"",INDEX('Page A'!F:F,MATCH('Page B'!A9,'Page
A'!A:A,0))) and it keeps returning a #N/A when the MATCH() function errors
out, but what I want to show is "" (blank space). Obviously there is
something wrong with my formula but I can't figure it out. If anyone out
there can help me please let me know what I am doing wrong

Thanks!

Thanks all for the Help, I got what I needed.

Cheers!

"Peo Sjoblom" wrote:

I am assuming you have a typo in the formula and Mar A should be Page A (or
if it is a translation that you missed?), however I am going to use Page A


=IF(ISNUMBER(MATCH('Page B'!A9,'Page A'!A:A,0)),INDEX('Page
A'!F:F,MATCH('Page B'!A9,'Page A'!A:A,0)),"")


should work


Regards,

Peo Sjoblom

"BJ" wrote:

Hello,

I am using the following function =IF(INDEX('Page A'!F:F,MATCH('Page
B'!A9,'Mar A'!A:A,0))=#N/A,"",INDEX('Page A'!F:F,MATCH('Page B'!A9,'Page
A'!A:A,0))) and it keeps returning a #N/A when the MATCH() function errors
out, but what I want to show is "" (blank space). Obviously there is
something wrong with my formula but I can't figure it out. If anyone out
there can help me please let me know what I am doing wrong

Thanks!