to lookup, get, offset or ??

Richard

Are the combinations of values unique? I'm trying to figure out wheteher an exact match is needed wtr both altitude and temperature. You could perhaps post here 5 rows of figures.

Aladin

Hi Richard

If I have understood you correctly, you could use the DGET function for this.

Lets say your table to look in is within the range A1:L500

AS AN EXAMPLE ONLY:
You have the heading "Altitude" in cell A1.
You have the heading "Temperature" in cell B1.
And the heading "Weights" in Cell C1.

Copy these the two headings "Altitude" and "Temperature" to cells M1:N1 respectively.

Below M1 ("Altitude") in cell M2 put your altitude.

Below N1 ("Temperature") in cell N2 put your temperature.

Now in any cell put the formula:

=DGET(A1:L500,"Weights",M1:N2)

To take this a step further you could create a Dynamic named range for your table and a static named range for the lookup criteria (M1:N2). To see how to create Dynamic Named Ranges follow my link and then click on "Dynamic Named Ranges".

Lets say you call your Dynamic Named Range "MyTable" and you call you static criteria range "Criteria" you could then use:

=DGET(MyTable,"Weights",Criteria)

If you have any problems creating the dynamic ranges lets me know.

Dave

OzGrid Business Applications

Is your data range set up something like Altitude across the top and Temperature down the side? If so, a combination of INDEX and MATCH functions may do the trick.

something like:
=INDEX(DataRange,MATCH(Temp,TempHeading,0),MATCH(Altitude,AltitudeHeading,0))

the '0' could be '1' or '-1' depending on how you want to match your data (look in Help under MATCH)

HTH

Mark

============
How to use "Double Lookup," if still needed considering other suggestions, like DGET?

Assuming the Altitude & Temperature data occupy A2:C40.
Insert a column before column C. The range for the data now becomes A2:D40.

In C2 enter: =A2&"-"&B2 [ copy down this to C3:C40 ]

Enter the criteria for Altitude and Temperature in F1 and G1 for which the associated Weight-value must be retrieved.

Select the range C2:D40 and name it WEIGHTS via the Name Box or the option Insert|Name|Define.

Use the following formula to get a weight given a set of criteria:

=VLOOKUP(F1&"-"&G1,C2:D40,2,0)

Caveat. I expect column C to contain distinct values.

Aladin

more info for you all

I have tried your sugestions and can only get on the board with a variation of Marks, =INDEX(OEI,MATCH(B24,Alt,1),MATCH(B25,Temp,-1))but the result is all over the place in the data range. I am about to look at Dave's Dynamic & Staic range stuff in a minute. if I could post a sample I would but cant on this page ? so a long example follows.
my Sample Data range, (I guess its an Array)
B6 to N18 (Named "OEI")contains numbers in ascending order largest in row 18 to smallest in row 6 & smallest in Column N to lagest in Column B. IE cell B18=5286 & N6=5073.

A6 to A18 (Named "Alt") contains Altitude from 6000 in A6 to 0 in A18 in 500 steps.

B5 to N5 (Named "Temp") contains Temperature left to Right, from -10 in B5 to 6 in N5.

hope it gives you more info thankyou for your efforts so far.

Re: more info for you all

How about:

=INDIRECT(ADDRESS(MATCH(B24;Alt;0)+5;MATCH(B25;Temp;0)+1))

where I assume a matrix of weight values in the range B6:N18. Alt refers to altitudes in A6:A18 and Temp to temperatures in B5:N5. An altitude criterium in B24 and a temperature criterium in B25.

Aladin

yes with a Hic up

Aladin thankyou very much, your last sugestion did the trick on my test sheet & when I expanded the range to the full Array it still works. the Hic up is when I try it on another sheet or work book with the same or similar data in multipul Arrays just changing the Range Names it will give the wrong answer so I have some checking to do. I will have fresh look tommorow night.
Thanks Again Richard