Thread: lookup in an array

Hi,

I need your help in writing a formula that will find a value in an array, based on the labels of the row and column of that value.
Labels are values in the first row and column of that array, that identifies the looked up cell (at the cross of that row and column).

Example:

Density Viscosity Temperature Colour
Water 0.457 3.55 500 NoColour
Oil 0.525 3.25 400 Yellow
Gas 0.616 2.93 300 Black
Wine 0.675 2.75 250 Red
Beer 0.746 2.57 200 Green
Creme 0.835 2.38 150 Wihite

ex.1:
Oil (first parameter of the formula)
Temperature (second parameter of the formula)
400 (formula needed, based on "Oil" and "Temperature")

ex.2:
Wine
Colour
Red (formula needed, based on "Wine" and "Colour")

It seems that is something simple, based on both hlookup and vlookup, but I'm not able to combine that functions....

Thanks a lot,

Nick

PS. Pls check the attached text file for a proper format.

If we assume data is listed in A:E headers in row 1 and types in A then

=INDEX($A:$E;MATCH("Oil";$A:$A;0);MATCH("Temperature"';$1:$1;0))

obviously constants (Oil, Temp) can be cell references to those cells containing items of interest.

EDIT:

Yes you could use VLOOKUP but it will generate greater calculation overheads (dependencies etc) -- INDEX/MATCH generally preferable and certainly more flexible I'd say.

=VLOOKUP("Oil",A:E,MATCH("Temperature",$1:$1,0),0)

use index with match on row/column