finding results in 3-D lookup table?

[Waxaholic]

I have a 3-D (i think)lookup table that i need to extract results from. It looks something like this:

NETWORK
SERVER 10K 20K 30K 40K 50K 60K 70K
100 25 30 35 40 45 50 55
200 26 31 36 41 46 51 56
300 28 33 38 43 47 53 57

My reference data is as follows:

Network SERVER
1436 123

What i am trying to do is lookup 1436 in the horizontal plane(NETWORK) and then lookup 123 in the SERVER column and find the intersected data which in this case would be 25 (rounding down). I have not had any luck with nesting functions. All i get is errors. Could someone point me in the right direction here or atleast to an article on nesting that might help. Greatly appreciated.

Wax

 

[JVFriederick]

Very quick and dirty:
Assume table is called "Data", and Network and Server refers to what is being looked up . . .

=VLOOKUP(Server,Data,HLOOKUP(Network,Data,1,TRUE)/1000+1,TRUE)

This method cheats because it divides the value obtained in the horizontal lookup by 1000, then adds 1 to get the proper column number for the vertical lookup.

Alternatively, you may insert a new row, showing the column numbers 2,3,4, 5 and so on. Then your equation would look like this :

=VLOOKUP(Server,Data,HLOOKUP(Network,Data,2,TRUE),TRUE)

Where row 2 contains the column numbers 2,3,4,5 and so on.

 

[Waxaholic]

Fantastic! It worked the first time. I really appreciate the help JVFriederick. This will help tremendously with some of the other lookup tables that i have yet to dig into. Again, thanks a bunch.

Wax

 

[JVFriederick]

No worries. Glad to know it helped.

If the table is a "long term" thing, consider adding lower and upper end values, such as Network=-1000 or 1000000 to catch data entry errors. Presently the formula will assign a value regardless of the reasonability.