Complex IF function involving matching multiple criteria

Hi,

I am working on providing the capacity over time for multiple different buildings and need some help with a formula. I have attached an example of what I am working on. I will refer to the top table as Table 1 and the bottom table as Table 2. The two tables shown are on different tabs in the workbook and there are a lot more buildings than shown which is why I would like to attempt some form of automation.

I need a formula in cell D13 of Table 2 (that can be copied across and down) that says:
If the building (B13 in Table 2) = the building in column A of Table 1 and the date in cell D12 of Table 2 = the date in E2 of Table 1 then subtract the corresponding "Seats Released at Exit Date" (J2) number in Table 1. If false then return the previous cell to the left (C13).

Feel free to ask clarifying questions. This is a little difficult to explain...

Thank you for any help!

Try:

=IF(VLOOKUP($B13,$A$2:$K$5,5,0)=D$12,C13-VLOOKUP($B13,$A$2:$K$5,10,0),C13)

This can be copied accross and down. If I've read this right then Building B1 will populate across with 2156 until 1Q 2015 and then it will change to -898 because you are subtracting 3054.

So in table 1 you will have multiple rows for a building, say B1 several times for example. If so the how is the last value used (Utilization(HC))? With your example this is the starting value for table 2 but if you have multiple instances of B1 in table 1 how do these work?

Can you upload a new example which will cover your problem and type in your result manually please (highlight in red or something so I know what to make a formula for). Also your first example shows that table 2 will be on a new sheet, if your example can have the tables on seperate sheets I can show you formulas which link to data on a different sheet if you aren't sure how to do this.

To do the subtraction as you have mentioned it will be a case of creating a unique identifier for each row in table 1 but I will explain this for you once done.

In D2:

=C2-SUMIFS('HMP_Building Lease Expiration D'!$L:$L,'HMP_Building Lease Expiration D'!$E:$E,D$1,'HMP_Building Lease Expiration D'!$A:$A,$B2)

Drag across/down. Check this works properly and let me know if there are any problems.

How this works:
Basically C2 is the previous number which you will subtract from (lets say 1000 for example)
Then ths SUMIFS will search the second sheet, when the building column matches the builing number AND the Exit By column matches the Quarter it adds the Seats Released values. So if you have two entries on sheet 2 for builing 1 that are in 2Q 2013 it will add these together (eg. 10 and 20 will become 30). So the formula will evaluate as:

=1000-(10+20) and then return 970.

To make this possible to copy across and down you need to use a mix of relative/absolute cell references. This is why there are cells in the formula with $ in use. This tells excel to keep the letter/number after the $ the same. so $A1 sopied to the right will still be $A1. without the $ it would be B1.

Hopefully this makes sense. Let me know if you need any further explination.