Correct #N/A Value
 

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(She et1!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(INDE X(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet 1!$F:$F,0),"
",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2) ,Sheet1!$F:$F,0)))))

can you help me correct it?

Correct #N/A Value
 

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(She et1!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(INDE X(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet 1!$F:$F,0),"
",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2) ,Sheet1!$F:$F,0)))))

can you help me correct it?

Correct #N/A Value
 

Try

=IF(ISNA(INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Shee t1!$A2),Sheet1!$F:$F,0))),"",INDEX(Sheet1!A:A,MATC H($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(She et1!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(INDE X(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet 1!$F:$F,0),"
",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2) ,Sheet1!$F:$F,0)))))

can you help me correct it?

Correct #N/A Value
 

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

--
__________________________________
HTH

Bob

"winnie123" wrote in message
...
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(She et1!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(INDE X(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet 1!$F:$F,0),"
",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2) ,Sheet1!$F:$F,0)))))

can you help me correct it?

Correct #N/A Value
 

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(Shee t1!$A2),Sheet1!$F:$F,0))),"",INDEX(Sheet1!A:A,MATC H($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(She et1!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(INDE X(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet 1!$F:$F,0),"
",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2) ,Sheet1!$F:$F,0)))))

can you help me correct it?

Correct #N/A Value
 

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(She et1!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(INDE X(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2),Sheet 1!$F:$F,0),"
",INDEX(Sheet1!A:A,MATCH($C$17&"_"&ROW(Sheet1!$A2) ,Sheet1!$F:$F,0)))))

can you help me correct it?

Correct #N/A Value
 

Welcome, good to hear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"winnie123" wrote in message
...
Thanks very much, it works a treat.