#### PabloMagnifico

##### New Member

- Joined
- Jan 31, 2018

- Messages
- 3

I'm stumped and need help. A new real estate investment property was purchased that contains vacant and occupied units. There are two kinds of vacant units: Vacant Penthouse Units (PH) and Vacant Units (or all others that aren’t PH). There are a total of 14 Vacant and Vacant PH units. There are 68 occupied units for your reference.

There are 8 different Lease Situation Descriptions that would determine what Leasing Commission (LC) % will be paid and what LC % was budgeted for:

New, No 3rd Party Broker

New, w/ 3rd Party Broker

Renewal, No 3rd Party Broker

Renewal, w/ 3rd Party Broker

New Penthouse, No Broker

New Penthouse, w/ Broker

Renewal Penthouse, No Broker

Renewal Penthouse, w/ Broker

(These are named (LeaseType_1 through 8 in the workbook)

The formula for the “Actual LC % to be Used” created works:

=IF($C$3=$C$8,6%,IF($C$3=$C$9,6%,IF($C$3=$C$10,2.5%,IF($C$3=$C$11,2.5%,IF($C$3=$C$12,6%,IF($C$3=$C$13,8%,IF($C$3=$C$14,2.5%,IF($C$3=$C$15,3%))))))))

The formula for the “Budget LC % to be Used” has been creating issues for me. I need the formula to read:

If Unit __ is located in cells J8-J13

__and__if the “New or Renewal Lease” (C3) is LeaseType_1 then use 6%; If Unit __ is located in cells J8-J13

__and__if C3 is LeaseType_2 then use 6%; If Unit __ is located in cells M8-M15

__and__if C3 is LeaseType_5 then use 8%; If Unit __ is located in cells M8-M15

__and__if C3 is LeaseType_6 then use 8%.

Those conditions need to be met first or have first priority. If those four conditions are false, then:

IF($C$3=$C$8,3.38%,IF($C$3=$C$9,3.38%,IF($C$3=$C$10,3.38%,IF($C$3=$C$11,3.38%,IF($C$3=$C$12,4.25%,IF($C$3=$C$13,4.25%,IF($C$3=$C$14,4.25%,IF($C$3=$C$15,4.25%,))))))))))))}

Basically LeaseType_1 through 4 would be 3.38% and LeaseType_5 through 8 would be 4.25%.

I keep getting #N/A.

Here's my crappy/ugly formula:

{=IF(AND(Unit=LOOKUP(Unit,J8:J13),$C$3=LeaseType_1),6%,IF(AND(Unit=LOOKUP(Unit,J8:J13),$C$3=LeaseType_2),IF(AND(Unit=LOOKUP(Unit,M8:M15),$C$3=LeaseType_5),8%,IF(AND(Unit=LOOKUP(Unit,M8:M15),$C$3=LeaseType_6),8%,IF($C$3=$C$8,3.38%,IF($C$3=$C$9,3.38%,IF($C$3=$C$10,3.38%,IF($C$3=$C$11,3.38%,IF($C$3=$C$12,4.25%,IF($C$3=$C$13,4.25%,IF($C$3=$C$14,4.25%,IF($C$3=$C$15,4.25%,))))))))))))}

Any and all help is greatly appreciated.

I’ve uploaded the workbook to DropBox (link below) but will email the work or get it to you via any method you prefer.

https://www.dropbox.com/sh/lwpxxmrzl3y9oby/AADSJ3V_PD8iyIySKaYMwJ_Ra?dl=0