| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Function with Multiple Conditions
Using Lookup I want to return a value to a cell based upon 2 conditions, which can exist in any one of four combinations: YY YN NY YN The first letter determines which of two Lookup tables are used (Lookup High or Lookup Std) the second determines which column in the relevant Lookup table to use for the value to be returned. I have the two following expressions each of which returns a correct value but only if the first letter condition is correct (Y or N). =IF($F2="YN",((INDEX('Lookup High'!$C$2:$D$45,MATCH(AM2,'Lookup High'!$C$2:$C$45,),MATCH(BM$1,'Lookup High'!$C$2:$D$2,)))),IF($F2="YY",(INDEX('Lookup High'!$E$2:$F$45,MATCH(AM2,'Lookup High'!$E$2:$E$45,),MATCH(BM$1,'Lookup High'!$E$2:$F$2,))))) =IF($F2="NN",((INDEX('Lookup STD'!$C$2:$D$45,MATCH(AM2,'Lookup STD'!$C$2:$C$45,),MATCH(BM$1,'Lookup STD'!$C$2:$D$2,)))),IF($F2="NY",(INDEX('Lookup STD'!$E$2:$F$45,MATCH(AM2,'Lookup STD'!$E$2:$E$45,),MATCH(BM$1,'Lookup STD'!$E$2:$F$2,))))) When I combine these: =IF($F2="YN",((INDEX('Lookup High'!$C$2:$D$45,MATCH(AM2,'Lookup High'!$C$2:$C$45,),MATCH(BM$1,'Lookup High'!$C$2:$D$2,)))),IF($F2="YY",(INDEX('Lookup High'!$E$2:$F$45,MATCH(AM2,'Lookup High'!$E$2:$E$45,),MATCH(BM$1,'Lookup High'!$E$2:$F$2,))))),IF($F2="NN",((INDEX('Lookup STD'!$C$2:$D$45,MATCH(AM2,'Lookup STD'!$C$2:$C$45,),MATCH(BM$1,'Lookup STD'!$C$2:$D$2,)))),IF($F2="NY",(INDEX('Lookup STD'!$E$2:$F$45,MATCH(AM2,'Lookup STD'!$E$2:$E$45,),MATCH(BM$1,'Lookup STD'!$E$2:$F$2,))))) Excel gives the result #VALUE! Any help in spotting the error would be appreciated. I would also like to return the value 0 if none of the conditions are met. Thanks in advance Tlosgyl3  -- tlosgyl3 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=559277 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Function with Multiple Conditions
Thanks I'll give that a try - its a new appraoch to me. Other ideas welcome tlosgyl3 -- tlosgyl3 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=559277 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Function with Multiple Conditions
I took the idea that Don Guillett offered up and ran it out a little further.
Here is the 'general' logic formula that I came up with: =IF(LEFT($F$2,1)="Y",IF(RIGHT($F$2,1)="Y","YY",IF( RIGHT($F$2,1)<"N","Y?","YN")),IF(LEFT($F$2,1)<"N ","??",IF(RIGHT($F$2,1)="Y","NY",IF(RIGHT($F$2,1)< "N","N?","NN")))) Anywhere there is a question mark in a potential output value, that's where you would want a zero. The outher output indications are where you'd substitute the appropriate INDEX() formula. Before going on, a couple of comments about the MATCH() statements inside of the INDEX() functions - I notice that in the 2nd MATCH() you use absolute row reference to BM$1, but in the first one you don't for AM2. Was that intentional? Also, in the matches, you've left the type of match empty, you could remove that last comma with the same result: default match type of 1 assumed. Saves you some characters in the formula entries. Now, by substituting the INDEX() formulas where there are logic result indicators like "YY" earlier, I end up with: =IF(LEFT($F$2,1)="Y",IF(RIGHT($F$2,1)="Y",INDEX(Lo okupHigh!$E$2:$F$45,MATCH(AM2,LookupHigh!$E$2:$E$4 5,),MATCH(BM$1,LookupHigh!$E$2:$F$2,))),IF(RIGHT($ F$2,1)<"N",0,INDEX(LookupHigh!$C$2:$D$45,MATCH(AM 2,LookupHigh!$C$2:$C$45,),MATCH(BM$1,LookupHigh!$C $2:$D$2,)))),IF(LEFT($F$2,1)<"N",0,IF(RIGHT($F$2, 1)="Y",INDEX(LookupSTD!$E$2:$F$45,MATCH(AM2,Lookup STD!$E$2:$E$45,),MATCH(BM$1,LookupSTD!$E$2:$F$2,)) ,IF(RIGHT($F$2,1)<"N",0,INDEX(LookupSTD!$C$2:$D$4 5,MATCH(AM2,LookupSTD!$C$2:$C$45,),MATCH(BM$1,Look upSTD!$C$2:$D$2,))))) I hope these ideas help some. Since I don't have your LookupSTD or LookupHigh sheets and data available it's a little difficult to test and I'm kind of rushed to come up with some test data to check it out with. "tlosgyl3" wrote: Using Lookup I want to return a value to a cell based upon 2 conditions, which can exist in any one of four combinations: YY YN NY YN The first letter determines which of two Lookup tables are used (Lookup High or Lookup Std) the second determines which column in the relevant Lookup table to use for the value to be returned. I have the two following expressions each of which returns a correct value but only if the first letter condition is correct (Y or N). =IF($F2="YN",((INDEX('Lookup High'!$C$2:$D$45,MATCH(AM2,'Lookup High'!$C$2:$C$45,),MATCH(BM$1,'Lookup High'!$C$2:$D$2,)))),IF($F2="YY",(INDEX('Lookup High'!$E$2:$F$45,MATCH(AM2,'Lookup High'!$E$2:$E$45,),MATCH(BM$1,'Lookup High'!$E$2:$F$2,))))) =IF($F2="NN",((INDEX('Lookup STD'!$C$2:$D$45,MATCH(AM2,'Lookup STD'!$C$2:$C$45,),MATCH(BM$1,'Lookup STD'!$C$2:$D$2,)))),IF($F2="NY",(INDEX('Lookup STD'!$E$2:$F$45,MATCH(AM2,'Lookup STD'!$E$2:$E$45,),MATCH(BM$1,'Lookup STD'!$E$2:$F$2,))))) When I combine these: =IF($F2="YN",((INDEX('Lookup High'!$C$2:$D$45,MATCH(AM2,'Lookup High'!$C$2:$C$45,),MATCH(BM$1,'Lookup High'!$C$2:$D$2,)))),IF($F2="YY",(INDEX('Lookup High'!$E$2:$F$45,MATCH(AM2,'Lookup High'!$E$2:$E$45,),MATCH(BM$1,'Lookup High'!$E$2:$F$2,))))),IF($F2="NN",((INDEX('Lookup STD'!$C$2:$D$45,MATCH(AM2,'Lookup STD'!$C$2:$C$45,),MATCH(BM$1,'Lookup STD'!$C$2:$D$2,)))),IF($F2="NY",(INDEX('Lookup STD'!$E$2:$F$45,MATCH(AM2,'Lookup STD'!$E$2:$E$45,),MATCH(BM$1,'Lookup STD'!$E$2:$F$2,))))) Excel gives the result #VALUE! Any help in spotting the error would be appreciated. I would also like to return the value 0 if none of the conditions are met. Thanks in advance Tlosgyl3 -- tlosgyl3 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=559277 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Function with Multiple Conditions
Please excuse delay in replying. Thanks for your contribution - I'll get on with trying it out. The Match references are correct but the $ could now be dropped it is a legacy of my wanting to drag the expression down through a column. My tables are a bit chunky to post here. Let me give your solution a try and get back to you. Thanks Tlosgyl3 -- tlosgyl3 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=559277 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Function with Multiple Conditions
Gentelmen - my apologies. Your are corresponding with a man who can't count brackets. Once corected to =IF(AM2<0,(IF($F2="YN",((INDEX(High!$C$2:$D$45,MA TCH(AM2,High!$C$2:$C$45,),MATCH(BM$1,High!$C$2:$D$ 2,)))),IF($F2="YY",((INDEX(High!$E$2:$F$45,MATCH(A M2,High!$E$2:$E$45,),MATCH(BM$1,High!$E$2:$F$2,))) ),IF($F2="NN",((INDEX(Std!$C$2:$D$45,MATCH(AM2,Std !$C$2:$C$45,),MATCH(BM$1,Std!$C$2:$D$2,)))),IF($F2 ="NY",((INDEX(Std!$E$2:$F$45,MATCH(AM2,Std!$E$2:$E $45,),MATCH(BM$1,Std!$E$2:$F$2,))))))))),0) It computes correctly tlosgyl3http://www.excelforum.com/images/smilies/eek.gif  -- tlosgyl3 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=559277 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Function with Multiple Conditions
Don't feel bad, figuring out how many closing parenthesis and where they need
to be is a continuing problem around my house also. Glad you got it working. Usually better to have something working that you understand than to have something working that, should it break, you'd have a dickens of a time repairing. That's one reason I sometimes stick with the "old ways" rather than jumping on some suggested esoteric code in a MSFT KnowledgeBase article. "tlosgyl3" wrote: Gentelmen - my apologies. Your are corresponding with a man who can't count brackets. Once corected to =IF(AM2<0,(IF($F2="YN",((INDEX(High!$C$2:$D$45,MA TCH(AM2,High!$C$2:$C$45,),MATCH(BM$1,High!$C$2:$D$ 2,)))),IF($F2="YY",((INDEX(High!$E$2:$F$45,MATCH(A M2,High!$E$2:$E$45,),MATCH(BM$1,High!$E$2:$F$2,))) ),IF($F2="NN",((INDEX(Std!$C$2:$D$45,MATCH(AM2,Std !$C$2:$C$45,),MATCH(BM$1,Std!$C$2:$D$2,)))),IF($F2 ="NY",((INDEX(Std!$E$2:$F$45,MATCH(AM2,Std!$E$2:$E $45,),MATCH(BM$1,Std!$E$2:$F$2,))))))))),0) It computes correctly tlosgyl3http://www.excelforum.com/images/smilies/eek.gif -- tlosgyl3 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=559277 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use IF function for two conditions | Excel Worksheet Functions | |||
| Logiacl function with multiple conditions | Excel Worksheet Functions | |||
| COUNTIF - multiple conditions | Excel Worksheet Functions | |||
| Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
| Vlookup with multiple conditions | Excel Worksheet Functions | |||
Linear Mode
