CODEWITHSUNDEEP
excel-formulaexcel-2010
Sage Mohan
Sage Mohan

Reputation: 155

Conditional picking of a row in excel

I have the following table in excel:

 Col1   Col2   Col3   Col4   Col5   Col6
 43401   17    #N/A    17    #N/A   43401
 43480   17    #N/A    17    #N/A   43480
 44501  #N/A   #N/A   #N/A   #N/A   
 97590  #N/A   #N/A   #N/A   #N/A   
 00475  #N/A   #N/A    8     #N/A

The formula is supposed to check if any of the cols(1-5) is not #N/A, then fetch corresponding Col1 value, if not put a blank. The formula that am currently using is giving me an error or row 5 as it has the value 8 in there and should give me back the corresponding Col1 value.

This the formula that am currently using:

=IFERROR(IF(G2<>"#N/A",F2,IF(H2<>"#N/A",F2,IF(I2<>"#N/A",F2,IF(J2<>"#N/A",F2,0)))),"")

In this formula above G2 is col2, row1 and F2 is col1, row 2...and so on.

Any help on why its happening?

Upvotes: 1

Views: 90

Answers (2)

Aafaque Abdullah
Aafaque Abdullah

Reputation: 371

This is happening because of "#N/A".

IFERROR function traps and handles errors produced by other formulas or functions so it specifically checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

So Whenever your functions check for "#N/A" to be true it throws an error and print a blank space.

Either change "#N/A" to "N/A" or use formula below

=IF(OR(NOT(ISNA(G2)),NOT(ISNA(H2)),NOT(ISNA(I2)),NOT(ISNA(J2))),F2,0)

It will give you this output

Col1    Col2    Col3    Col4    Col5    Col6
43401   17      #N/A    17      #N/A    43401
43480   17      #N/A    17      #N/A    43480
44501   #N/A    #N/A    #N/A    #N/A    0
97590   #N/A    #N/A    #N/A    #N/A    0
475     #N/A    8       #N/A    #N/A    475

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Try this

=IF(COUNTIF(G2:J2,"#N/A")<4,F2,"")

In your current formula the #N/A leads to error results in the IF statement, and your outer IfError just returns the blank.

Your logic can be expressed differently by counting the NA values. If there are fewer than four, one of them cannot be NA, so then return the value from column F.

Upvotes: 1

Related Questions