Hello,

I have created a formula using the threads I have seen and I can not get rid
of the #N/A value. The formula works ok if there is match or if the value of
$c$2 is not in my Sheet1.

Original formula was

=IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))

The #N/A appears when it has found a MATCH but there is only 1 row of data.

I have tried ISNA but my logic is not right, so it returns a value of TRUE.

Here is my revised formula


=IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0),"
",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))))

can you help me correct it?

Try the below (all in one cell, split for easier clarity)
Copy direct from the post, then paste directly into the formula bar:
=IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",
IF(ISNA(MATCH($C$17&"_"&ROW($A2),Sheet1!$F:$F,0)),"",
INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW($A2),Sheet1!$F:$F,0))))

Works ok? Click YES below to celebrate the success
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"winnie123" wrote:
> I have created a formula using the threads I have seen and I can not get rid
> of the #N/A value. The formula works ok if there is match or if the value of
> $c$2 is not in my Sheet1.
>
> Original formula was
>
> =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))
>
> The #N/A appears when it has found a MATCH but there is only 1 row of data.
>
> I have tried ISNA but my logic is not right, so it returns a value of TRUE.
>
> Here is my revised formula
>
> =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0),"
> ",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))))
>
> can you help me correct it?
>

Try

=IF(ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0))),"",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))

Mike

"winnie123" wrote:

> Hello,
>
> I have created a formula using the threads I have seen and I can not get rid
> of the #N/A value. The formula works ok if there is match or if the value of
> $c$2 is not in my Sheet1.
>
> Original formula was
>
> =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))
>
> The #N/A appears when it has found a MATCH but there is only 1 row of data.
>
> I have tried ISNA but my logic is not right, so it returns a value of TRUE.
>
> Here is my revised formula
>
>
> =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0),"
> ",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))))
>
> can you help me correct it?
>

=IF(OR(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),ISNA(MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0))),"",
INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))

--
__________________________________
HTH

Bob

"winnie123" <(E-Mail Removed)> wrote in message
news:A82AC51C-F631-4260-87C4-(E-Mail Removed)...
> Hello,
>
> I have created a formula using the threads I have seen and I can not get
> rid
> of the #N/A value. The formula works ok if there is match or if the value
> of
> $c$2 is not in my Sheet1.
>
> Original formula was
>
> =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))
>
> The #N/A appears when it has found a MATCH but there is only 1 row of
> data.
>
> I have tried ISNA but my logic is not right, so it returns a value of
> TRUE.
>
> Here is my revised formula
>
>
> =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0),"
> ",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))))
>
> can you help me correct it?
>

Hi,

I don't suppose it makes a lot of diffferenece but

&"_"&ROW(Sheet1!$A2)

simply returns _2 so there's no need to refer to another sheet

&"_"&ROW($A2),

will do it.

Mike

"Mike H" wrote:

> Try
>
> =IF(ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0))),"",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))
>
> Mike
>
> "winnie123" wrote:
>
> > Hello,
> >
> > I have created a formula using the threads I have seen and I can not get rid
> > of the #N/A value. The formula works ok if there is match or if the value of
> > $c$2 is not in my Sheet1.
> >
> > Original formula was
> >
> > =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))
> >
> > The #N/A appears when it has found a MATCH but there is only 1 row of data.
> >
> > I have tried ISNA but my logic is not right, so it returns a value of TRUE.
> >
> > Here is my revised formula
> >
> >
> > =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0),"
> > ",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))))
> >
> > can you help me correct it?
> >

Thanks very much, it works a treat.

"Max" wrote:

> Try the below (all in one cell, split for easier clarity)
> Copy direct from the post, then paste directly into the formula bar:
> =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",
> IF(ISNA(MATCH($C$17&"_"&ROW($A2),Sheet1!$F:$F,0)),"",
> INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW($A2),Sheet1!$F:$F,0))))
>
> Works ok? Click YES below to celebrate the success
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:23,000 Files:370 Subscribers:66
> xdemechanik
> ---
> "winnie123" wrote:
> > I have created a formula using the threads I have seen and I can not get rid
> > of the #N/A value. The formula works ok if there is match or if the value of
> > $c$2 is not in my Sheet1.
> >
> > Original formula was
> >
> > =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))
> >
> > The #N/A appears when it has found a MATCH but there is only 1 row of data.
> >
> > I have tried ISNA but my logic is not right, so it returns a value of TRUE.
> >
> > Here is my revised formula
> >
> > =IF(ISNA(MATCH($C$17,Sheet1!$C:$C,0)),"",ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0),"
> > ",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet1!$F:$F,0)))))
> >
> > can you help me correct it?
> >

Welcome, good to hear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"winnie123" <(E-Mail Removed)> wrote in message
news:66D617C2-E6EC-4BFF-9BB7-(E-Mail Removed)...
> Thanks very much, it works a treat.