find all cells that match and use in an index/vlookup
 

I have a LARGE set of data...

I parse it to a pivot.
red blue
Chair living room .1 .2
dining room .6 .3
bedroom .3 .4
sofa living room .9 .2
dining room .01 0
bedroom .2 .5

etc...
(4 colums, multiple rows)
I need to be able to:
Find what room a soffa is most likely to be in, and what that chance is, and
use that data in another worksheet where there will just be a list of
furniture, in room, with max chance.

I can seem to figure how to find the range of rows that have chairs (there
are not always the same amount of rooms in a list, sometime 2 someteims
20...) and use that found range in a vlookup or index to find the room.

find all cells that match and use in an index/vlookup
 

Maybe more precicely... here is my starting data:

ZONE_ID ZONE_NAME COUNT(ZN.ZCMD_ID) ACTIVE ITEM_NUMBER NAME AVG_ITEMS_PER_HOUR
1495 takj_121 9 0 22504 Ivory 0.0003125
1496 takj_125 9 0 22504 Ivory 0.0003125
1497 takj_130 9 0 22504 Ivory 0.0003125
16 Beholder 23 -1 11790 Jade Shard 0.07728
26 CSHome 1 -1 11790 Jade Shard 0
70 Cauldron 4 -1 11790 Jade Shard 0.192
48 CazicThule 53 -1 11790 Jade Shard 0.174289655172414

I then on antoher worksheet have a list
Ivory
Jade Shard

I need to find: Max AVG_ITEMS_PER_HOUR and the ZONE_NAME that that applies
to, as well as a total of AVG_ITEMS_PER_HOUR (which I can do easily enough
with a pivot table. a pivot table for "max" seems to show again to much data
so I cannot get out the data I need.