Replacing IF /AND formula with VLOOKUP

[brentc]

Ok so this formula does work but breaks after to many if and statements. How can I preform the same task just in a better way?

=IF(sheet1!D2=sheet2!$A$3,AND(sheet1!E2=sheet2!$C$1,sheet2!$C$3)
,IF(sheet1!D2=sheet2!$A$3,AND(sheet1!E2=sheet2!$D$1,sheet2!$D$3)
,IF(sheet1!D2=sheet2!$A$3,AND(sheet1!E2=sheet2!$E$1,sheet2!$E$3)
,IF(sheet1!D2=sheet2!$A$3,AND(sheet1!E2=sheet2!$F$1,sheet2!$F$3)
,IF(sheet1!D2=sheet2!$A$4,AND(sheet1!E2=sheet2!$C$1,sheet2!$C$4)
,IF(sheet1!D2=sheet2!$A$4,AND(sheet1!E2=sheet2!$D$1,sheet2!$D$4)
,IF(sheet1!D2=sheet2!$A$4,AND(sheet1!E2=sheet2!$E$1,sheet2!$E$4)
,IF(sheet1!D2=sheet2!$A$4,AND(sheet1!E2=sheet2!$F$1,sheet2!$F$4)
,IF(sheet1!D2=sheet2!$A$5,AND(sheet1!E2=sheet2!$C$1,sheet2!$C$5)
,IF(sheet1!D2=sheet2!$A$5,AND(sheet1!E2=sheet2!$D$1,sheet2!$D$5)
,IF(sheet1!D2=sheet2!$A$5,AND(sheet1!E2=sheet2!$E$1,sheet2!$E$5)
,IF(sheet1!D2=sheet2!$A$5,AND(sheet1!E2=sheet2!$F$1,sheet2!$F$5)))))))))))))

Thanks :P

[DonkeyOte]

Welcome to the Board.

Could you explain what exactly this monstrosity is meant to be doing ? I can't make head nor tail of it...

presently it's saying (excuse annotation)

=IF(x=y,AND(z=a,s<>0),IF(x=y,z=b,t<>0)....

which basically makes no sense...

I suspect perhaps it's meant to be saying

=IF(AND(x=y,z=a),s,IF(AND(x=y,z=b),t,...)

Which can be streamlined for sure but we need to understand the aim.

[martindwilson]

funny that ,all i can make it do is return TRUE or possibly False i got bored trying it!

[DonkeyOte]

I wonder if perhaps the below could replace your formula ?

Please Login or Register  to view this content.

[brentc]

Welcome to the Board.

Could you explain what exactly this monstrosity is meant to be doing ? I can't make head nor tail of it...

presently it's saying (excuse annotation)

=IF(x=y,AND(z=a,s<>0),IF(x=y,z=b,t<>0)....

which basically makes no sense...

I suspect perhaps it's meant to be saying

=IF(AND(x=y,z=a),s,IF(AND(x=y,z=b),t,...)

Which can be streamlined for sure but we need to understand the aim.

Yes this is very close to what I am trying to accomplish.
=IF(x=y,AND(z=a,s),IF(x=y,z=b,t) - kind of like this. But still not sure how to write it.
So let me explain.

I have a matrix on sheet2 which looks like this:

Please Login or Register  to view this content.

Sheet1 I have two columns where each column populates a number from a previous calculation. This number is used in the formula to locate the desired field in the matrix above on Sheet2.
So if D2 is 2 and E2 is 3 the answer is "no". Again, if D2 is 3 and E2 is 1 the answer would be "nice". So the numbers are only used as reference to where in the matrix it should look up.

I think that is a bit more clear.

Thanks all.

[DonkeyOte]

Please see the suggested VLOOKUP formula.

On an aside... re: IF AND syntax

=IF(AND(a=b,c=d,e=f),"yes","no")

So the above return "yes" if a=b and c=d and e=f else "no" would be returned.

[modytrane]

see attached example.
does that help?
modytrane

[brentc]

Thanks for all the help, Vlookup ended up being the answer. I really appreciate it.