# Help NEEDED for complex formula

#### snewsome

##### New Member
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)

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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,"")))

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

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

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

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!

Replies
15
Views
950
Replies
2
Views
203
Replies
6
Views
5K
Replies
2
Views
311
Replies
7
Views
466

1,203,082
Messages
6,053,424
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back