I would have thought that you can get to this result by making a copy
of the pivot table on another sheet then:

1. Double-click the building code heading and select no subtotals
2. Drag the operating unit heading right and up a cell to the column
heading position.
3. Uncheck any other items on the dropdown of the operating unit you
don't want to show (Blanks, etc.)

Alternatively, if your list is fixed and difficult to change you should
be able to use the following formula on sheet1:

=SUMPRODUCT(--(LOOKUP(ROW(Code),ROW(Code)/(Code<""),Code)=$A2),--(Unit=B$1),Total)

Where the names Code, Unit and Total refer to the A,B,C columns of the
pivot table.
Either replace these by the actual ranges or select A3:A6000 and type
"Code" in the name box next to the formula bar and do the same for Unit
and Total.

Spidey wrote:

I am not sure if this can be done and i have spent days trying to get this to
work (please don't tell my boss), but i have invested so much time, i can't
turn back now...any help would be extremely appreciated

I have a pivot table (example below) which sums the number of people (over
6,000 people in the actual table) that work in a building (100+building in
the actual table), by operating unit. I have a list which contains the
corresponding "building codes." I am trying to write a vlookup formula that
will look to the building code in the list, then return the # of employees in
that building, in a particular operating unit, from the pivot table. The data
in the pivot table will be changing weekly.

So, in Column "B" below, the formula would look to the pivot, see building
code 11 and return the number 16.

List in Sheet1
A B C
Building Code #of Accounting employees #of IT employees
11

__________________________________________________ _

PIVOT TABLE:
A B C
3Q06
BUILDING CODE OPERATING UNIT Total people
11 Accounting 16
IT 4
11 grand Total 20
__________________________________________________ _