Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting the correct SUM answer | Excel Discussion (Misc queries) | |||
If with AND - Is this correct? | Excel Worksheet Functions | |||
Linking to correct row | Excel Discussion (Misc queries) | |||
If correct, add 1 | Excel Worksheet Functions | |||
if(b2=1,b3,0) is this correct | Excel Worksheet Functions |