Lori,

That is an amazing formula and works really well for calculating the values
where there is an exact match. However, I can't work out the significance
of "F3" in the second formula - I get a #NUM! error....

The idea is to use the values in "tbl" to lookup and interpolate
NS_Wave_height,NS_Wave_period, and NS_Wave_dir - I calculate WaterLvl
elsewhere as a input to the lookup.

Can you tell me how to fix the forumla? I have never used MMULT,
Percentrank, or percentile before - although I will be looking up these
functions to workout how you got these formulas.

Thanks for your help.

Mark

"Lori" wrote:

Let "Tbl" be the matrix (Hs,Ts,WaveDir,WindSpd,WaterLvl,WindDir)
Then with corresponding lookup values in B2:G2 of a new sheet, try entering
in H2:

=LOOKUP(2,1/(MMULT(--(Tbl=B2:G2),{1;1;1;1;1;1})=6),NS_Wave_Height)

and similar formulas for NS Wave Period, NS Wave Dir & Ref.
which should give 0.61814, 2.2983, 254.577& aabbgb respectively.
[You can use insertNamesCreate to create the column names]
For interpolating water level values try:

=PERCENTILE(IF(MMULT(--(Tbl=B2:G2),{1;1;1;1;0;1})=5,NS_Wave_Height),
PERCENTRANK(IF(MMULT(--(Tbl=B2:G2),{1;1;1;1;0;1})=5,WaterLvl),F3,30))

With WaterLvl =6.25, NS Wave Height=0.4535075,NS Wave Period=1.88265
(WaveDir is not reliable as it fluctuates and can cross 360 to 0.)