multiple array reference with lookup combination

This is something I like to do without resorting to vba...

I have a huge table which looks roughly like:

Please Login or Register  to view this content.

In this table I want to randomly (from 1 to 20) pick an item from each room i.e. "Kitchen" and "5" which should result in "Knife".

-The random part is easy and done.
-Usually you can use vlookup to find "5" in an ascending list
However in order to do that you first need to 'restrict' the search to the "Kitchen" array--> vlookup(5;C5:D7;2)

I did find a way to build a string of the kitchen array which results in "C5:D7"
You can't however feed a string into a formula and I have no clue how 'convert' a string into a cell reference.

So an answer to either (or both):
-how can you convert a string into a reference?
-is there a (another)way to find a random item in the kitchen?

thanks in advance.

Martin,

Thanks for the input, but unfortunately your way off, rearranging the table is one of the things I cant do, for one, there are more columns:
i.e

Please Login or Register  to view this content.

The other is that the indexing is NOT random, its a distribution of chance to find the item in the room, if you look at the table you notice that:
- there is a 4 in 20 chance to find the screwdriver
- there is a 4 in 20 chance to find the hammer
- there is a 7 in 20 chance to find the pliers
- there is a 6 in 20 chance to find the tacker

so you're answer won't work.... thanks anyway.

Well I have found a way to do it, but I'm still curious to alternatives.
Give the table:

Please Login or Register  to view this content.

(yes the first row is empty and used in the formula!)

the formula used in B16 (where the correct result is) is:

Please Login or Register  to view this content.

it was quite a hassle to figure that one out. Essentially: "OFFSET" is the key to this problem