I have 4 columns in an excel sheet with three columns containing some values including some #N/A values, some blanks and some "Unknown" and some valid values in all 3 columns. I want to pick the valid values from all these columns into the fourth columns with column#1 as priority if not N/A, then Column#2, followed by the third Column and such that the N/A, empty and Unknown values are omitted. I have created a nested IF with ISNA and OR function but it is not working.

Any idea/help would be appreciated

message edited by imadnanfarooq

What does the "valid data" look like? Is it text, numbers, something else? Perhaps a short example of your data layout would help so that we know what we are dealing with.

Please click on the following link before posting your example data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

I want to pick the valid values from all these columns into the fourth columnsthat the N/A, empty and Unknown values are omittedTry this:

With your Data in Columns A & B & C in cell D1 enter this formula:

=IF(AND(A1<>"N/A",A1<>"Unknown",A1<>""),A1,IF(AND(B1<>"N/A",B1<>"Unknown",B1<>""),B1,IF(AND(C1<>"N/A",C1<>"Unknown",C1<>""),C1,"")))

See if that is what you want.

MIKE

Hi,

Thanks for your replies. I have tried using the below formula but it does not work.=IF(AND(NOT(ISNA(X2)),X2<>"Unknown"),X2,IF(AND.......

It looks like the above always returns true and it gets data from X2 only. #N/A are the values as a result of Formulas.

Any help would be appreciatedHere is how it looks like

Name X Y Z Useful-Data

Alpha #N/A HP Cisco HP

Beta #N/A #N/A Cisco Cisco

Gamma Cisco #N/A Unknown Cisco

Delta HP #N/A #N/A HP

Zeta Unknown #N/A Microsoft Microsoft

Please click on the following How-To link and read the instructions on how to post example data in this forum. Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll