Help NEEDED for complex formula

[snewsome]

Hey everyone, I've been getting help building on this formula but I'm afraid the logic has gotten more complex in what I need to return. Here's what they're asking for now:

If M5=1,2,3,4,5,6,or 9 then return H5 a period and data from E5, but if M5=1,2,3,4,5,6,or 9 and L5=M then return 7 digits from H5 & ".71020"; then if M5=5,6,or 9 and D5="su" then return 7 digits from H5 & ".59010.

Hope that makes sense.

This is what I currently have that I’m trying to build off of:

=IF(OR(M5={5,6}),IF(M5="{5,6}",H5&"."&E5,IF(L5="M",(LEFT(H5,7))&".71020",LEFT(H5,7)&".59010")),H5&"."&E5)

 

[svendiamond]

I like those brackets around the numbers to look up but for some reason it's not working for me. I haven't ever used that {1,2,3,4,5} as a range before... I'm doing something wrong but I don't know what. So I had to use long "OR" statements here but this should work I hope?

=IF(AND(OR(M5=5,M5=6,M5=9),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5=1,M5=2,M5=3,M5=4,M5=5,M5=6,M5=5,M5=6,M5=9,),L5="M"),H5&".71020",IF(AND(OR(M5=1,M5=2,M5=3,M5=4,M5=5,M5=6,M5=5,M5=6,M5=9,),L5<>"M"),H5&"."&E5,"")))

 

[snewsome]

I like those brackets around the numbers to look up but for some reason it's not working for me. I haven't ever used that {1,2,3,4,5} as a range before... I'm doing something wrong but I don't know what. So I had to use long "OR" statements here but this should work I hope?

=IF(AND(OR(M5=5,M5=6,M5=9),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5=1,M5=2,M5=3,M5=4,M5=5,M5=6,M5=5,M5=6,M5=9,),L5="M"),H5&".71020",IF(AND(OR(M5=1,M5=2,M5=3,M5=4,M5=5,M5=6,M5=5,M5=6,M5=9,),L5<>"M"),H5&"."&E5,"")))

This is awesome..thanks so much! I adjusted the formula with brackets, and worked great!!

=IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&"."&E5,"")))

 

[snewsome]

I like those brackets around the numbers to look up but for some reason it's not working for me. I haven't ever used that {1,2,3,4,5} as a range before... I'm doing something wrong but I don't know what. So I had to use long "OR" statements here but this should work I hope?

=IF(AND(OR(M5=5,M5=6,M5=9),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5=1,M5=2,M5=3,M5=4,M5=5,M5=6,M5=5,M5=6,M5=9,),L5="M"),H5&".71020",IF(AND(OR(M5=1,M5=2,M5=3,M5=4,M5=5,M5=6,M5=5,M5=6,M5=9,),L5<>"M"),H5&"."&E5,"")))

Another question..do you know how to adjust the formula at the end to instead of returning a period and E5..to just do a vlookup? Here's what I got, but I may have the ending wrong:

=IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,TYPE!A:C,3,TRUE)))))

 

[snewsome]

Another question..do you know how to adjust the formula at the end to instead of returning a period and E5..to just do a vlookup? Here's what I got, but I may have the ending wrong:

=IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,TYPE!A:C,3,TRUE)))))

I think I got it. Thanks again!!!

 

[Cosmic Wizard]

I think I got it. Thanks again!!!

Hey snewsome, you couldn't post some data so others could see how the formula works please?
It just look amazing but it a bit hard to comprehend without some spreadsheet data, thankyou!