Home 
Search 
Today's Posts 
#1




Nested IF AND returning #VALUE!
I have what I thought was a simple formula I wanted to return a value based on the condition (TRUE or FALSE) of two cells. They are either both true, both false, one is false and the other true, or one is true and the other false.
IF(AND(ER18=TRUE,ET18=TRUE),ER22),IF(AND(ER18=FALS E,ET18=TRUE),ER23),IF(AND(ER18=TRUE,ET18=FALSE),ER 24),IF(AND(ER18=FALSE,ET18=FALSE),ER25) This is giving me a result of "#VALUE!" I do this so seldom, that I'm sure I've got it wrong but, it's 1AM and I'm not seeing it. Any help, very very much appreciated! Paul 
#2




Nested IF AND returning #VALUE!
Hi Paul,
Am Thu, 7 Jul 2016 22:27:58 0700 (PDT) schrieb Paul Doucette: IF(AND(ER18=TRUE,ET18=TRUE),ER22),IF(AND(ER18=FALS E,ET18=TRUE),ER23),IF(AND(ER18=TRUE,ET18=FALSE),ER 24),IF(AND(ER18=FALSE,ET18=FALSE),ER25) try: =IF(AND(ER18=TRUE,ET18=TRUE),ER22,IF(AND(ER18=FALS E,ET18=TRUE),ER23,IF(AND(ER18=TRUE,ET18=FALSE),ER2 4,IF(AND(ER18=FALSE,ET18=FALSE),ER25)))) Regards Claus B.  Windows10 Office 2016 
#3




Nested IF AND returning #VALUE!
Hi Paul,
Am Fri, 8 Jul 2016 09:42:55 +0200 schrieb Claus Busch: IF(AND(ER18=TRUE,ET18=TRUE),ER22),IF(AND(ER18=FALS E,ET18=TRUE),ER23),IF(AND(ER18=TRUE,ET18=FALSE),ER 24),IF(AND(ER18=FALSE,ET18=FALSE),ER25) try: =IF(AND(ER18=TRUE,ET18=TRUE),ER22,IF(AND(ER18=FALS E,ET18=TRUE),ER23,IF(AND(ER18=TRUE,ET18=FALSE),ER2 4,IF(AND(ER18=FALSE,ET18=FALSE),ER25)))) a little shorter: =INDEX(ER22:ER25,MATCH(ER18&ET18,{"11","01","10","00"},0)) Regards Claus B.  Windows10 Office 2016 
#4




Nested IF AND returning #VALUE!
On Friday, July 8, 2016 at 4:09:03 AM UTC4, Claus Busch wrote:
Hi Paul, Am Fri, 8 Jul 2016 09:42:55 +0200 schrieb Claus Busch: IF(AND(ER18=TRUE,ET18=TRUE),ER22),IF(AND(ER18=FALS E,ET18=TRUE),ER23),IF(AND(ER18=TRUE,ET18=FALSE),ER 24),IF(AND(ER18=FALSE,ET18=FALSE),ER25) try: =IF(AND(ER18=TRUE,ET18=TRUE),ER22,IF(AND(ER18=FALS E,ET18=TRUE),ER23,IF(AND(ER18=TRUE,ET18=FALSE),ER2 4,IF(AND(ER18=FALSE,ET18=FALSE),ER25)))) a little shorter: =INDEX(ER22:ER25,MATCH(ER18&ET18,{"11","01","10","00"},0)) Regards Claus B.  Windows10 Office 2016 So, you are like, a genius. :) And you,ve made my day! THANK YOU! Paul 
#5




Nested IF AND returning #VALUE!
Hi Paul,
Am Fri, 8 Jul 2016 05:27:58 0700 (PDT) schrieb Paul Doucette: So, you are like, a genius. :) And you,ve made my day! THANK YOU! you are welcome. Always glad to help. Regards Claus B.  Windows10 Office 2016 
#6




Nested IF AND returning #VALUE!
Wizard!!!
 Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion  This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus 
#7




Nested IF AND returning #VALUE!
Hi Garry,
Am Fri, 08 Jul 2016 09:22:59 0400 schrieb GS: Wizard!!! I don't like nested IF formulas. Easily you lose track of things. With the latest update of Excel 2016 there is the function IFS where you can insert 127 arguments. What a nonsense. Regards Claus B.  Windows10 Office 2016 
#8




Nested IF AND returning #VALUE!
On Friday, July 8, 2016 at 10:47:48 AM UTC4, Claus Busch wrote:
Hi Garry, Am Fri, 08 Jul 2016 09:22:59 0400 schrieb GS: Wizard!!! I don't like nested IF formulas. Easily you lose track of things. With the latest update of Excel 2016 there is the function IFS where you can insert 127 arguments. What a nonsense. Regards Claus B.  Windows10 Office 2016 127?!? Agreed. Too convoluted. I need to learn how to use the Index and Match functions. It appears much more straightforward... but I will have to do some studying. Right now I only know that it works, not how. Many thanks, Paul 
#9




Nested IF AND returning #VALUE!
Hi Paul,
Am Fri, 8 Jul 2016 10:13:15 0700 (PDT) schrieb Paul Doucette: 127?!? Agreed. Too convoluted. I need to learn how to use the Index and Match functions. It appears much more straightforward... but I will have to do some studying. Right now I only know that it works, not how. the INDEX describes a range and MATCH will find the row number. The different result of ER18&ET18 are into the array in the MATCH formula. Regards Claus B.  Windows10 Office 2016 
#10




Nested IF AND returning #VALUE!
Hi Paul,
Am Fri, 8 Jul 2016 10:13:15 0700 (PDT) schrieb Paul Doucette: 127?!? Agreed. Too convoluted. I need to learn how to use the Index and Match functions. It appears much more straightforward... but I will have to do some studying. Right now I only know that it works, not how. from the beginning on: The formula for INDEX is =INDEX(range, row, column) So you only have one column column is not needed. You have to find the row of your range. Therefore I converted your boolean values with the double minus to numeric values. TRUE=1 FALSE=0 Then I combined both cells with the ampersand TRUE&TRUE="11" FALSE&TRUE="01" and so on. Using the ampersand the result becomes text. That is why it is in quotes into the array. =MATCH(ER18&ET18,{"11","01","10","00"},0) looks for your combination of your cells If you have in ER18 TRUE and in ET18 FALSE you get "10". MATCH finds this value in third place of the array. So your expected output is the third row of your range which is ER24. Regards Claus B.  Windows10 Office 2016 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Nested If not returning correct values  Excel Discussion (Misc queries)  
nested IF(OR Vlookup returning zero instead of value in target cel  Excel Worksheet Functions  
Nested If statements returning a sum calculation  Excel Worksheet Functions  
Nested if returning error 1004  Excel Programming  
Nested "if" not returning expected value  Excel Worksheet Functions 