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))

 

[tlosgyl3]

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

tlosgyl3

 

[Guest]

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]

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

 

[Guest]

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.