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?
|