If Function with Multiple Conditions to be met first

[PabloMagnifico]

Using Excel 2016. Windows 7.
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

 

[thisoldman]

I hope this is some help for you. Your formula is returning #N/A because the first lookup in the first tested condition is failing: the lookup returns #N/A. Excel knows #N/A is not TRUE and not FALSE, so it returns #N/A for the whole formula evaluation.

Here's what I think you mean for the second formula.
If the unit is vacant then,
if the unit is a penthouse, return 8%,
else, if the unit is not a penthouse, return 6%.
Otherwise, when the unit is occupied, lookup the value from C3 in the lease descriptions and return the matching commission.

Knowing that the list of vacant units might shrink or grow, I used the range J8:J23 for the vacant non-penthouse units and the range L8:L23 for the vacant penthouse units. Try this formula in C18:

=IF(COUNTIF($J$8:$J$23,Unit),6%,IF(COUNTIF($L$8:$L$23,Unit),8%,INDEX($E$8:$E$15,MATCH($C$3,$C$8:$C$15,0))))


You might try this alternative for your first formula:

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

If you collapse your table in columns G and H to a single column, you could use a much simpler (less nesting) lookup formula instead of your first formula:

	G
5	LC % to be Used by Owner
6	Leasing Commission
7
8	6.00%
9	6.00%
10	2.50%
11	2.50%
12	6.00%
13	8.00%
14	2.50%
15	3.00%

<tbody>
</tbody>

Either

=VLOOKUP($C$3,C8:G15,5,FALSE)

or

=INDEX($G$8:$G$15,MATCH($C$3,$C$8:$C$15,0))

would work.


As a side note, you uploaded your file after zipping it. Excel xlsx and xlsm files are already zipped files. When you zip them again, there is a possibility of the file size increasing rather than decreasing. This obviously didn't happen in this instance.

 

[PabloMagnifico]

Incredible. Your solutions to the first formula are much better than the nesting quagmire I created. I will go with the VLOOKUP solution since it would be the easiest to explain to others if need be. I personally need to experiment and practice more with the INDEX and MATCH functions.

Looks like you are spot on with the solution to my second formula. Thank you immensely. You are correct the list might shrink or grow but only shrink in the instances where those Vacant units become occupied and therefore on the second round of leasing they fall under the Weighted Average LC bucket, and grow if similar units were to be combined. Example being 2-APH and 2-BCDEPH becoming 2-ABCDEPH. That new unit would need to be manually entered into the list.

This brings up a new question/can of worms. What if a new tenant were to lease unit 2-D and unit 2-BCDEPH? One applies 6% LC and the other applies 8% LC to their rental amount. Obviously only half of the info needed has been provided, so I won't trouble anyone with that; unless you feel up to the challenge.

I can't thank you enough. You've been beyond helpful.

-P

 

[thisoldman]

I'm happy to have helped.

For your new question, I can't think of any way better to cope with the combination other than to treat them as two separate transactions.

 

[PabloMagnifico]

That's what I plan on doing.
Also I forgot to mention the zipped file dilemma. I believe DropBox, Box, Sharefile, etc., all throw everything into a compressed folder when downloading, even if it's only one file or if it's multiple files together. That's been my experience. Otherwise in this instance I only did a drag-and-drop into DropBox without compressing anything prior. So the compressing was out of my hands.

Thanks again for everything. I'm sure I'll be posting more questions in this forum as time goes on.