ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Nexsted if across 3 columns (https://www.excelbanter.com/excel-worksheet-functions/6477-nexsted-if-across-3-columns.html)

Felicity Geronimo
Nexsted if across 3 columns
 
Hi,

I need to check 3 columns nx to each other to see if true or false
appears in these cells for example:

A B C
FALSE TRUE FALSE
#N/A #N/A #N/A
FALSE FALSE TRUE
FALSE FALSE TRUE
FALSE FALSE TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
TRUE FALSE FALSE

I want to write a formula that says if column A = false, check column
B, if that also says false check column C

If True appears in column A i want it to display column A
If True appears in column B i want it to display column B
If True appears in column C i want it to display column C
If any column contains #N/A i want it to move to next column and check
again
If all 3 columns contain #N/A I want it to display "Not found"

Thanks

Jason Morin
Try this in D1 and fill down:

=IF(ISNUMBER(MATCH(TRUE,A1:C1,0)),TRUE,"Not Found")

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi,

I need to check 3 columns nx to each other to see if
true or false
appears in these cells for example:

A B C
FALSE TRUE FALSE
#N/A #N/A #N/A
FALSE FALSE TRUE
FALSE FALSE TRUE
FALSE FALSE TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
TRUE FALSE FALSE

I want to write a formula that says if column A = false,
check column
B, if that also says false check column C

If True appears in column A i want it to display column A
If True appears in column B i want it to display column B
If True appears in column C i want it to display column C
If any column contains #N/A i want it to move to next
column and check
again
If all 3 columns contain #N/A I want it to display "Not
found"

Thanks
.

Ron Rosenfeld
On 16 Nov 2004 06:08:39 -0800, (Felicity Geronimo)
wrote:

Hi,

I need to check 3 columns nx to each other to see if true or false
appears in these cells for example:

A B C
FALSE TRUE FALSE
#N/A #N/A #N/A
FALSE FALSE TRUE
FALSE FALSE TRUE
FALSE FALSE TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
TRUE FALSE FALSE

I want to write a formula that says if column A = false, check column
B, if that also says false check column C

If True appears in column A i want it to display column A
If True appears in column B i want it to display column B
If True appears in column C i want it to display column C
If any column contains #N/A i want it to move to next column and check
again
If all 3 columns contain #N/A I want it to display "Not found"

Thanks

Your description implies that the columns may contain ONLY TRUE, FALSE or #N/A
and also that there will only be a single TRUE.

The following formula appears to do what you describe:

=IF(COUNTIF(A1:C1,NA())=3,"not found",
CHOOSE(MATCH(TRUE,A1:C1,0),"column A","column B","columnC"))


--ron

Bernard Liengme
If you want a true naive formula!!!
=IF(ISNA(C1),IF(ISNA(B1),IF(A1,"Col A","?"),IF(B1,"Col B",IF(A1,"Col A"))),
IF(C1,"Col C", IF(ISNA(B1),IF(A1,"Col A","?"),IF(B1,"Col B",IF(A1,"Col
A")))))

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Felicity Geronimo" wrote in message
om...
Hi,

I need to check 3 columns nx to each other to see if true or false
appears in these cells for example:

A B C
FALSE TRUE FALSE
#N/A #N/A #N/A
FALSE FALSE TRUE
FALSE FALSE TRUE
FALSE FALSE TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
TRUE FALSE FALSE

I want to write a formula that says if column A = false, check column
B, if that also says false check column C

If True appears in column A i want it to display column A
If True appears in column B i want it to display column B
If True appears in column C i want it to display column C
If any column contains #N/A i want it to move to next column and check
again
If all 3 columns contain #N/A I want it to display "Not found"

Thanks

Felicity Geronimo
Thnakyou very much, this works perfectly well - howver, please can you
let me know what the "?" actually does?

Many Thanks




"Bernard Liengme" wrote in message ...
If you want a true naive formula!!!
=IF(ISNA(C1),IF(ISNA(B1),IF(A1,"Col A","?"),IF(B1,"Col B",IF(A1,"Col A"))),
IF(C1,"Col C", IF(ISNA(B1),IF(A1,"Col A","?"),IF(B1,"Col B",IF(A1,"Col
A")))))

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Felicity Geronimo" wrote in message
om...
Hi,

I need to check 3 columns nx to each other to see if true or false
appears in these cells for example:

A B C
FALSE TRUE FALSE
#N/A #N/A #N/A
FALSE FALSE TRUE
FALSE FALSE TRUE
FALSE FALSE TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
#N/A #N/A TRUE
TRUE FALSE FALSE

I want to write a formula that says if column A = false, check column
B, if that also says false check column C

If True appears in column A i want it to display column A
If True appears in column B i want it to display column B
If True appears in column C i want it to display column C
If any column contains #N/A i want it to move to next column and check
again
If all 3 columns contain #N/A I want it to display "Not found"

Thanks


All times are GMT +1. The time now is 11:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com