Second question - see this thread f

[Hasit]

Second question - see this thread for related info: thread68-184936

Heres (another) one I have been wrestling with for a few hours. I am using Excel 2000.

I have 4 columns and 4 columns, in (unnamed) tables. They are:

TABLE 1
Column A: Unique (building) key
Column B: Square footage per building
Column C: A value of either "bid", "contract" or "core"
Column D: A value of either "occupied", "part occupied", "surrendered" or "vacant"

TABLE 2
Column H: Unique (building) key (same as above)
Column I: A value of either "datacentre" or "office"
Column J: A square footage value for datacentres only
Column K: Building cost

I need to do this using formulas only (no VBA, macros or otherwise for various reasons I will not bore you with):

I need to compare the uniqe building keys (which are the same numbers for the same properties in both tables). If there is a match, then I would like to take only those buildings that are designated "occupied" or "part occupied", then take the square footage in table 2 (sqft for the datacentre) and divide it by the total sqft value in table 1 that matches the unique building key.

If possible I would like THEN to take that value and multiply it by the building cost in table 2, all in one formula. I have got some of the way there, but not quite.

Also, I would prefer not to name the tables, as I am going to be making wholesale changes to the data and structures very soon. This is kind of an immediate requirement.

If I have to do it in two steps, fine, but any help would be appreciated.

Any thoughts??

 

[Dreamboat]

Try this?

=IF(OR(D2="O",D2="PO&quot,(VLOOKUP(A2,$H$2:$K$8,3,0)/B2)*VLOOKUP(A2,$H$2:$K$8,4,0),"Not found&quot

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[Dreamboat]

PS: I used rows only up to 8, and O means occupied and PO means part occupied, etc. You figured that our already, right?

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[Dreamboat]

Sorry--the "not found" part doesn't work. Let me know if you need it.

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[Hasit]

Dreamboat: I will try out your suggestion - thanks! For some reason, I used VLOOKUP and it did not work for me, but looking at your construct, there is a difference.

As for "not found", nope, I don't need it.

 

[Hasit]

p.s. Dreamboat, have you looked at the first part of the question (the link is in the body of the question - Thread68-184936)

 

[Hasit]

The VLOOKUP seems to work OK, BUT only if I remove the last ",0" from the VLOOKUP. Strange.

But heres a second quirky thing: If I use the formula as it stands (up to 8 rows), everything works fine. If I then increase it to say 100 rows, everything still works fine. If I then increase the number of rows in the range to something over 200, I get the wrong result (I have around 900 rows in the range).

I have tried several things including adding the last ,0 to VLOOKUP (in which case I get a #N/A! returned), naming the range (same incorrect result), reducing the number of columns in the range (same incorrect result).

Am I doing something stupid, or is there something wrong with VLOOKUP??!!

 

[Hasit]

OK. I have solved my last posted question above, but WHY it now works is still a mystery. Heres the scenario:

The range I am using is $D$3:$AJ$900, within which the specific data I looking at is in column E, before performing calculations with data in other columns.

The VLOOKUP formula in question is:

=IF(OR(CV3="Occupied",CV3="Part Occupied&quot,(VLOOKUP(CT3,$D$3:$AJ$900,7)/CS3)*VLOOKUP(AT3,$D$3:$AJ$900,11))

Maddeningly, it does not work. If I now change the formula is:

=IF(OR(CV3="Occupied",CV3="Part Occupied&quot,(VLOOKUP(CT3,$e$3:$AJ$900,6)/CS3)*VLOOKUP(AT3,$e$3:$AJ$900,10))

this works beautifully. Anyone have any ideas why??