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 <at>yahoo<dot>com
----
BJ <[email protected]> wrote in message
news:[email protected]...
> 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!