Matrix lookup/mulitple criteria lookup
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.)
|