If Function with Multiple Conditions

[tlosgyl3]

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

[Don Guillett]

how about using left and right something like this idea

=VLOOKUP(A3,IF(LEFT(A2)="y",D4:E6,E4:F6),IF(RIGHT(A2)="n",1,2))
--
Don Guillett
SalesAid Software
[email protected]
"tlosgyl3" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[tlosgyl3]

Thanks I'll give that a try - its a new appraoch to me.
Other ideas welcome

tlosgyl3

[JLatham]

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(LookupHigh!$E$2:$F$45,MATCH(AM2,LookupHigh!$E$2:$E$45,),MATCH(BM$1,LookupHigh!$E$2:$F$2,))),IF(RIGHT($F$2,1)<>"N",0,INDEX(LookupHigh!$C$2:$D$45,MATCH(AM2,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,LookupSTD!$E$2:$E$45,),MATCH(BM$1,LookupSTD!$E$2:$F$2,)),IF(RIGHT($F$2,1)<>"N",0,INDEX(LookupSTD!$C$2:$D$45,MATCH(AM2,LookupSTD!$C$2:$C$45,),MATCH(BM$1,LookupSTD!$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
>
>

[tlosgyl3]

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]

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,MATCH(AM2,High!$C$2:$C$45,),MATCH(BM$1,High!$C$2:$D$2,)))),IF($F2="YY",((INDEX(High!$E$2:$F$45,MATCH(AM2,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

[JLatham]

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,MATCH(AM2,High!$C$2:$C$45,),MATCH(BM$1,High!$C$2:$D$2,)))),IF($F2="YY",((INDEX(High!$E$2:$F$45,MATCH(AM2,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
>
>