Matrix lookup/mulitple criteria lookup

[MarkFranklin]

Hi, I am using Excel 2003, I have a big matrix of data which basically has
colums called "ref", Hs, TS, Wave Dir, Windspd, Waterlvl, winddir, NS Wave
height, NS wave period, NS wave dir, ref check"
What I want to be able to do is lookup values for Hs, Ts, Wavedir, windspd,
water level and winddir and return values for NS wave height, nS wave period
and NS wave dir. I have pasted a table into this post! So if I want to
lookup
Hs=0.1,Ts =4, Wave dir=210, Winspd=10,waterlvl=11, and wave dir=210
I would get the answers "0.61814, 2.2983, 254.577, aabbgb" (preferably in
seperate cells). The "ref and Ref check" are there for my help - I have
found a way to do this by giving each parameter value a letter.
In the future I'd like to use values that need to interpolated as well (i.e
Waterlvl =6.25) which would need to be interpolated - so any diea on how to
do that would be good

Thanks

mark

-Table





ref Hs Ts WaveDir WindSpd WaterLvl WindDir NS Wave Height NS Wave period NS
Wave dir ref check
aaabaa 0.1 4 180 10 5 180 0.22851 1.265 194.019 aaabaa
aaabba 0.1 4 180 10 6 180 0.2649 1.3501 205.303 aaabba
aaabca 0.1 4 180 10 7 180 0.29599 1.4076 207.914 aaabca
aaabda 0.1 4 180 10 8 180 0.32192 1.4706 212.133 aaabda
aaabea 0.1 4 180 10 9 180 0.34608 1.5324 214.416 aaabea
aaabfa 0.1 4 180 10 10 180 0.36737 1.5903 216.189 aaabfa
aaabga 0.1 4 180 10 11 180 0.38251 1.6236 217.482 aaabga
aaabha 0.1 4 180 10 12 180 0.38931 1.6439 218.145 aaabha
aabbab 0.1 4 210 10 5 210 0.37453 1.687 253.814 aabbab
aabbbb 0.1 4 210 10 6 210 0.44139 1.8522 256.279 aabbbb
aabbcb 0.1 4 210 10 7 210 0.48986 1.974 256.77 aabbcb
aabbdb 0.1 4 210 10 8 210 0.53127 2.0756 256.675 aabbdb
aabbeb 0.1 4 210 10 9 210 0.56404 2.162 256.102 aabbeb
aabbfb 0.1 4 210 10 10 210 0.59595 2.2366 254.847 aabbfb
aabbgb 0.1 4 210 10 11 210 0.61814 2.2983 254.577 aabbgb
aabbhb 0.1 4 210 10 12 210 0.63583 2.3442 254.193 aabbhb
aacbac 0.1 4 240 10 5 240 0.47071 2.0335 270.391 aacbac
aacbbc 0.1 4 240 10 6 240 0.56295 2.2634 272.935 aacbbc
aacbcc 0.1 4 240 10 7 240 0.62622 2.4036 272.934 aacbcc
aacbdc 0.1 4 240 10 8 240 0.67778 2.5134 272.179 aacbdc
aacbec 0.1 4 240 10 9 240 0.72352 2.607 271.279 aacbec
aacbfc 0.1 4 240 10 10 240 0.76224 2.6921 270.115 aacbfc
aacbgc 0.1 4 240 10 11 240 0.79921 2.7592 269.21 aacbgc
aacbhc 0.1 4 240 10 12 240 0.82773 2.8173 268.347 aacbhc
aadbad 0.1 4 270 10 5 270 0.54654 2.274 280.991 aadbad
aadbbd 0.1 4 270 10 6 270 0.67161 2.5471 284.539 aadbbd
aadbcd 0.1 4 270 10 7 270 0.75745 2.6887 285.759 aadbcd
aadbdd 0.1 4 270 10 8 270 0.81664 2.7999 285.914 aadbdd
aadbed 0.1 4 270 10 9 270 0.86643 2.8917 285.458 aadbed
aadbfd 0.1 4 270 10 10 270 0.91162 2.9573 284.847 aadbfd
aadbgd 0.1 4 270 10 11 270 0.9464 3.0265 284.215 aadbgd
aadbhd 0.1 4 270 10 12 270 0.97862 3.0769 283.528 aadbhd
aaebae 0.1 4 300 10 5 300 0.59403 2.3762 291.494 aaebae
aaebbe 0.1 4 300 10 6 300 0.75842 2.704 295.721 aaebbe
aaebce 0.1 4 300 10 7 300 0.8685 2.8943 298.555 aaebce
aaebde 0.1 4 300 10 8 300 0.94451 3.0173 300.514 aaebde
aaebee 0.1 4 300 10 9 300 1.00183 3.11 301.896 aaebee
aaebfe 0.1 4 300 10 10 300 1.04883 3.1745 302.923 aaebfe
aaebge 0.1 4 300 10 11 300 1.08486 3.2327 303.848 aaebge
aaebhe 0.1 4 300 10 12 300 1.11278 3.2749 304.514 aaebhe
aafbaf 0.1 4 330 10 5 330 0.59136 2.3122 304.291 aafbaf
aafbbf 0.1 4 330 10 6 330 0.76895 2.6649 307.562 aafbbf
aafbcf 0.1 4 330 10 7 330 0.89026 2.8879 310.371 aafbcf
aafbdf 0.1 4 330 10 8 330 0.97672 3.0235 312.352 aafbdf
aafbef 0.1 4 330 10 9 330 1.04134 3.1389 314.071 aafbef
aafbff 0.1 4 330 10 10 330 1.0923 3.2147 315.31 aafbff
aafbgf 0.1 4 330 10 11 330 1.13446 3.2868 316.305 aafbgf
aafbhf 0.1 4 330 10 12 330 1.16534 3.3386 317.348 aafbhf
aagbag 0.1 4 360 10 5 360 0.52959 2.0681 328.239 aagbag
aagbbg 0.1 4 360 10 6 360 0.67869 2.4047 326.17 aagbbg
aagbcg 0.1 4 360 10 7 360 0.77451 2.6046 325.478 aagbcg
aagbdg 0.1 4 360 10 8 360 0.84725 2.7487 325.705 aagbdg
aagbeg 0.1 4 360 10 9 360 0.90205 2.8617 326.185 aagbeg
aagbfg 0.1 4 360 10 10 360 0.94659 2.9496 326.689 aagbfg
aagbgg 0.1 4 360 10 11 360 0.98096 3.0263 327.198 aagbgg
aagbhg 0.1 4 360 10 12 360 1.00895 3.0898 327.686 aagbhg
ababaa 0.1 6 180 10 5 180 0.22707 1.2592 195.885 ababaa
ababba 0.1 6 180 10 6 180 0.26279 1.3445 203.044 ababba
ababca 0.1 6 180 10 7 180 0.29654 1.4099 208.32 ababca
ababda 0.1 6 180 10 8 180 0.32247 1.4773 212.676 ababda
ababea 0.1 6 180 10 9 180 0.34752 1.5307 214.703 ababea
ababfa 0.1 6 180 10 10 180 0.3684 1.5874 216.185 ababfa
ababga 0.1 6 180 10 11 180 0.38235 1.6266 217.56 ababga
ababha 0.1 6 180 10 12 180 0.39059 1.6529 218.76 ababha
abbbab 0.1 6 210 10 5 210 0.37404 1.6881 253.828 abbbab
abbbbb 0.1 6 210 10 6 210 0.43942 1.8446 256.107 abbbbb
abbbcb 0.1 6 210 10 7 210 0.48848 1.971 256.524 abbbcb
abbbdb 0.1 6 210 10 8 210 0.53012 2.0769 256.443 abbbdb
abbbeb 0.1 6 210 10 9 210 0.56374 2.1602 255.874 abbbeb
abbbfb 0.1 6 210 10 10 210 0.59449 2.239 254.703 abbbfb
abbbgb 0.1 6 210 10 11 210 0.61938 2.3026 254.499 abbbgb
abbbhb 0.1 6 210 10 12 210 0.63798 2.3511 254.159 abbbhb
abcbac 0.1 6 240 10 5 240 0.47719 2.0168 269.854 abcbac
abcbbc 0.1 6 240 10 6 240 0.57228 2.254 272.564 abcbbc
abcbcc 0.1 6 240 10 7 240 0.64205 2.395 272.602 abcbcc
abcbdc 0.1 6 240 10 8 240 0.69598 2.5036 271.986 abcbdc
abcbec 0.1 6 240 10 9 240 0.7397 2.6043 271.034 abcbec
abcbfc 0.1 6 240 10 10 240 0.77807 2.687 269.998 abcbfc
abcbgc 0.1 6 240 10 11 240 0.81206 2.7503 269.073 abcbgc
abcbhc 0.1 6 240 10 12 240 0.83943 2.804 268.197 abcbhc
abdbad 0.1 6 270 10 5 270 0.56119 2.2503 280.63 abdbad
abdbbd 0.1 6 270 10 6 270 0.69021 2.5228 284.43 abdbbd
abdbcd 0.1 6 270 10 7 270 0.77544 2.6636 285.703 abdbcd
abdbdd 0.1 6 270 10 8 270 0.8392 2.7755 286.151 abdbdd
abdbed 0.1 6 270 10 9 270 0.89024 2.8716 285.754 abdbed
abdbfd 0.1 6 270 10 10 270 0.93361 2.9403 284.967 abdbfd
abdbgd 0.1 6 270 10 11 270 0.96912 3.0013 284.155 abdbgd
abdbhd 0.1 6 270 10 12 270 1.00118 3.0557 283.566 abdbhd
abebae 0.1 6 300 10 5 300 0.5987 2.3594 291.216 abebae
abebbe 0.1 6 300 10 6 300 0.76243 2.6758 295.816 abebbe
abebce 0.1 6 300 10 7 300 0.86962 2.8615 298.922 abebce
abebde 0.1 6 300 10 8 300 0.94254 2.9775 300.867 abebde
abebee 0.1 6 300 10 9 300 1.0016 3.0652 302.146 abebee
abebfe 0.1 6 300 10 10 300 1.046 3.128 303.406 abebfe
abebge 0.1 6 300 10 11 300 1.07818 3.1898 304.247 abebge
abebhe 0.1 6 300 10 12 300 1.10869 3.2339 304.705 abebhe
abfbaf 0.1 6 330 10 5 330 0.59064 2.3135 304.347 abfbaf
abfbbf 0.1 6 330 10 6 330 0.7681 2.6629 307.621 abfbbf
abfbcf 0.1 6 330 10 7 330 0.89145 2.8862 310.501 abfbcf
abfbdf 0.1 6 330 10 8 330 0.97897 3.0319 312.421 abfbdf
abfbef 0.1 6 330 10 9 330 1.0408 3.1416 313.893 abfbef
abfbff 0.1 6 330 10 10 330 1.09016 3.2199 315.276 abfbff
abfbgf 0.1 6 330 10 11 330 1.13429 3.2898 316.412 abfbgf
abfbhf 0.1 6 330 10 12 330 1.1688 3.3432 317.346 abfbhf
abgbag 0.1 6 360 10 5 360 0.53041 2.0863 327.1 abgbag
abgbbg 0.1 6 360 10 6 360 0.68007 2.4027 326.062 abgbbg
abgbcg 0.1 6 360 10 7 360 0.77707 2.6042 325.431 abgbcg
abgbdg 0.1 6 360 10 8 360 0.84517 2.7486 325.7 abgbdg
abgbeg 0.1 6 360 10 9 360 0.90366 2.8643 326.047 abgbeg
abgbfg 0.1 6 360 10 10 360 0.94855 2.955 326.526 abgbfg
abgbgg 0.1 6 360 10 11 360 0.9832 3.0259 327.001 abgbgg
abgbhg 0.1 6 360 10 12 360 1.01422 3.0847 327.475 abgbhg
acabaa 0.1 8 180 10 5 180 0.22826 1.2717 194.69 acabaa
acabba 0.1 8 180 10 6 180 0.26376 1.3465 203.216 acabba
acabca 0.1 8 180 10 7 180 0.29669 1.4158 208.804 acabca
acabda 0.1 8 180 10 8 180 0.32521 1.4985 215.741 acabda
acabea 0.1 8 180 10 9 180 0.34749 1.5478 217.505 acabea
acabfa 0.1 8 180 10 10 180 0.3686 1.611 218.85 acabfa
acabga 0.1 8 180 10 11 180 0.38303 1.6465 220.149 acabga
acabha 0.1 8 180 10 12 180 0.39001 1.6727 220.977 acabha
acbbab 0.1 8 210 10 5 210 0.37291 1.6907 253.873 acbbab
acbbbb 0.1 8 210 10 6 210 0.44066 1.8537 256.175 acbbbb
acbbcb 0.1 8 210 10 7 210 0.48952 1.9728 256.667 acbbcb
acbbdb 0.1 8 210 10 8 210 0.53245 2.0791 256.44 acbbdb
acbbeb 0.1 8 210 10 9 210 0.56294 2.1641 255.964 acbbeb
acbbfb 0.1 8 210 10 10 210 0.59694 2.2431 254.759 acbbfb
acbbgb 0.1 8 210 10 11 210 0.6194 2.3042 254.459 acbbgb
acbbhb 0.1 8 210 10 12 210 0.63768 2.3542 254.181 acbbhb
accbac 0.1 8 240 10 5 240 0.48486 2.0062 269.45 accbac
accbbc 0.1 8 240 10 6 240 0.58293 2.2362 272.011 accbbc
accbcc 0.1 8 240 10 7 240 0.64818 2.3777 272.226 accbcc
accbdc 0.1 8 240 10 8 240 0.70245 2.4793 271.684 accbdc
accbec 0.1 8 240 10 9 240 0.74163 2.5667 270.882 accbec
accbfc 0.1 8 240 10 10 240 0.77596 2.6414 269.685 accbfc
accbgc 0.1 8 240 10 11 240 0.80962 2.7074 268.599 accbgc
accbhc 0.1 8 240 10 12 240 0.83562 2.7577 267.634 accbhc
acdbad 0.1 8 270 10 5 270 0.56151 2.2515 280.56 acdbad
acdbbd 0.1 8 270 10 6 270 0.69668 2.5012 284.143 acdbbd
acdbcd 0.1 8 270 10 7 270 0.78017 2.6402 285.358 acdbcd
acdbdd 0.1 8 270 10 8 270 0.84056 2.7458 285.505 acdbdd
acdbed 0.1 8 270 10 9 270 0.89133 2.8361 285.23 acdbed
acdbfd 0.1 8 270 10 10 270 0.93755 2.9574 285.273 acdbfd
acdbgd 0.1 8 270 10 11 270 0.97326 3.0179 284.684 acdbgd
acdbhd 0.1 8 270 10 12 270 1.0049 3.0634 283.863 acdbhd
acebae 0.1 8 300 10 5 300 0.60199 2.336 291.317 acebae
acebbe 0.1 8 300 10 6 300 0.7649 2.6519 295.877 acebbe
acebce 0.1 8 300 10 7 300 0.87231 2.8298 298.988 acebce
acebde 0.1 8 300 10 8 300 0.94855 2.9431 300.843 acebde
acebee 0.1 8 300 10 9 300 1.00642 3.0359 302.263 acebee
acebfe 0.1 8 300 10 10 300 1.05127 3.1011 303.378 acebfe
acebge 0.1 8 300 10 11 300 1.08817 3.1597 304.333 acebge
acebhe 0.1 8 300 10 12 300 1.11716 3.1991 304.95 acebhe
acfbaf 0.1 8 330 10 5 330 0.5928 2.2904 304.582 acfbaf
acfbbf 0.1 8 330 10 6 330 0.77043 2.637 307.81 acfbbf
acfbcf 0.1 8 330 10 7 330 0.8947 2.8563 310.707 acfbcf
acfbdf 0.1 8 330 10 8 330 0.97832 2.9992 312.932 acfbdf
acfbef 0.1 8 330 10 9 330 1.04665 3.1006 314.421 acfbef
acfbff 0.1 8 330 10 10 330 1.09185 3.2134 315.274 acfbff
acfbgf 0.1 8 330 10 11 330 1.13278 3.2801 316.521 acfbgf
acfbhf 0.1 8 330 10 12 330 1.16395 3.344 317.42 acfbhf
acgbag 0.1 8 360 10 5 360 0.53031 2.0875 327.064 acgbag
acgbbg 0.1 8 360 10 6 360 0.67849 2.4048 326.054 acgbbg
acgbcg 0.1 8 360 10 7 360 0.77715 2.6034 325.349 acgbcg
acgbdg 0.1 8 360 10 8 360 0.84528 2.7486 325.811 acgbdg
acgbeg 0.1 8 360 10 9 360 0.90321 2.8614 326.175 acgbeg
acgbfg 0.1 8 360 10 10 360 0.94639 2.9541 326.668 acgbfg
acgbgg 0.1 8 360 10 11 360 0.9806 3.0254 327.304 acgbgg
acgbhg 0.1 8 360 10 12 360 1.01207 3.0866 327.652 acgbhg
adabaa 0.1 10 180 10 5 180 0.22876 1.2712 195.178 adabaa
adabba 0.1 10 180 10 6 180 0.26608 1.353 205.785 adabba
adabca 0.1 10 180 10 7 180 0.29984 1.4223 211.728 adabca
adabda 0.1 10 180 10 8 180 0.32659 1.4934 215.481 adabda
adabea 0.1 10 180 10 9 180 0.3483 1.5515 217.693 adabea
adabfa 0.1 10 180 10 10 180 0.37049 1.6184 219.28 adabfa
adabga 0.1 10 180 10 11 180 0.38409 1.6487 220.225 adabga
adabha 0.1 10 180 10 12 180 0.39218 1.6759 221.29 adabha
adbbab 0.1 10 210 10 5 210 0.37405 1.6934 254.09 adbbab
adbbbb 0.1 10 210 10 6 210 0.44146 1.8585 256.312 adbbbb
adbbcb 0.1 10 210 10 7 210 0.49067 1.9775 256.781 adbbcb
adbbdb 0.1 10 210 10 8 210 0.53171 2.0837 256.607 adbbdb
adbbeb 0.1 10 210 10 9 210 0.56424 2.1656 256.105 adbbeb
adbbfb 0.1 10 210 10 10 210 0.59785 2.246 254.9 adbbfb
adbbgb 0.1 10 210 10 11 210 0.62146 2.3103 254.527 adbbgb
adbbhb 0.1 10 210 10 12 210 0.64009 2.3589 254.269 adbbhb
adcbac 0.1 10 240 10 5 240 0.48607 2.0072 269.529 adcbac
adcbbc 0.1 10 240 10 6 240 0.58327 2.239 272.015 adcbbc
adcbcc 0.1 10 240 10 7 240 0.64936 2.3788 272.284 adcbcc
adcbdc 0.1 10 240 10 8 240 0.70309 2.4831 271.704 adcbdc
adcbec 0.1 10 240 10 9 240 0.7433 2.5713 271.044 adcbec
adcbfc 0.1 10 240 10 10 240 0.78089 2.6492 269.835 adcbfc
adcbgc 0.1 10 240 10 11 240 0.81357 2.7118 268.785 adcbgc
adcbhc 0.1 10 240 10 12 240 0.84006 2.7646 267.739 adcbhc
addbad 0.1 10 270 10 5 270 0.56729 2.2341 280.304 addbad
addbbd 0.1 10 270 10 6 270 0.6989 2.5077 284.125 addbbd
addbcd 0.1 10 270 10 7 270 0.78293 2.6464 285.335 addbcd
addbdd 0.1 10 270 10 8 270 0.84679 2.758 285.628 addbdd
addbed 0.1 10 270 10 9 270 0.89847 2.8449 285.282 addbed
addbfd 0.1 10 270 10 10 270 0.93824 2.9227 284.731 addbfd
addbgd 0.1 10 270 10 11 270 0.9752 2.9807 283.942 addbgd
addbhd 0.1 10 270 10 12 270 1.00831 3.035 283.337 addbhd
adebae 0.1 10 300 10 5 300 0.60176 2.3388 291.352 adebae
adebbe 0.1 10 300 10 6 300 0.76561 2.6534 295.821 adebbe
adebce 0.1 10 300 10 7 300 0.87232 2.8381 298.841 adebce
adebde 0.1 10 300 10 8 300 0.94532 2.9525 300.837 adebde
adebee 0.1 10 300 10 9 300 1.00519 3.0393 302.01 adebee
adebfe 0.1 10 300 10 10 300 1.0498 3.1054 303.41 adebfe
adebge 0.1 10 300 10 11 300 1.08795 3.1535 304.26 adebge
adebhe 0.1 10 300 10 12 300 1.11263 3.196 304.722 adebhe
adfbaf 0.1 10 330 10 5 330 0.59332 2.2928 304.659 adfbaf
adfbbf 0.1 10 330 10 6 330 0.76997 2.6385 307.951 adfbbf
adfbcf 0.1 10 330 10 7 330 0.89273 2.8617 310.933 adfbcf
adfbdf 0.1 10 330 10 8 330 0.97915 2.9998 312.974 adfbdf
adfbef 0.1 10 330 10 9 330 1.0465 3.1042 314.53 adfbef
adfbff 0.1 10 330 10 10 330 1.09742 3.1908 315.988 adfbff
adfbgf 0.1 10 330 10 11 330 1.13805 3.2583 316.818 adfbgf
adfbhf 0.1 10 330 10 12 330 1.16933 3.3093 317.77 adfbhf
adgbag 0.1 10 360 10 5 360 0.5304 2.0702 328.105 adgbag
adgbbg 0.1 10 360 10 6 360 0.67856 2.4051 326.111 adgbbg
adgbcg 0.1 10 360 10 7 360 0.7764 2.6024 325.299 adgbcg
adgbdg 0.1 10 360 10 8 360 0.84685 2.7478 325.628 adgbdg
adgbeg 0.1 10 360 10 9 360 0.90527 2.8613 326.095 adgbeg
adgbfg 0.1 10 360 10 10 360 0.94714 2.9539 326.622 adgbfg
adgbgg 0.1 10 360 10 11 360 0.98518 3.025 327.285 adgbgg
adgbhg 0.1 10 360 10 12 360 1.01456 3.0865 327.776 adgbhg
aeabaa 0.1 12 180 10 5 180 0.22896 1.2733 195.12 aeabaa
aeabba 0.1 12 180 10 6 180 0.26496 1.3473 203.598 aeabba
aeabca 0.1 12 180 10 7 180 0.30026 1.4238 211.867 aeabca
aeabda 0.1 12 180 10 8 180 0.32748 1.4951 215.823 aeabda
aeabea 0.1 12 180 10 9 180 0.34928 1.5533 217.858 aeabea
aeabfa 0.1 12 180 10 10 180 0.37246 1.6197 219.406 aeabfa
aeabga 0.1 12 180 10 11 180 0.38534 1.6527 220.414 aeabga
aeabha 0.1 12 180 10 12 180 0.3925 1.6807 221.405 aeabha
aebbab 0.1 12 210 10 5 210 0.37458 1.6927 254.142 aebbab
aebbbb 0.1 12 210 10 6 210 0.44163 1.8598 256.383 aebbbb
aebbcb 0.1 12 210 10 7 210 0.49211 1.98 256.967 aebbcb
aebbdb 0.1 12 210 10 8 210 0.53327 2.0886 256.804 aebbdb
aebbeb 0.1 12 210 10 9 210 0.56606 2.169 256.202 aebbeb
aebbfb 0.1 12 210 10 10 210 0.59889 2.2549 255.028 aebbfb
aebbgb 0.1 12 210 10 11 210 0.62186 2.3107 254.581 aebbgb
aebbhb 0.1 12 210 10 12 210 0.64199 2.3636 254.35 aebbhb
aecbac 0.1 12 240 10 5 240 0.4867 2.0112 269.533 aecbac
aecbbc 0.1 12 240 10 6 240 0.58504 2.2383 272.123 aecbbc
aecbcc 0.1 12 240 10 7 240 0.65075 2.3787 272.359 aecbcc
aecbdc 0.1 12 240 10 8 240 0.70451 2.4856 271.795 aecbdc
aecbec 0.1 12 240 10 9 240 0.74525 2.5782 271.167 aecbec
aecbfc 0.1 12 240 10 10 240 0.78405 2.6526 270.009 aecbfc
aecbgc 0.1 12 240 10 11 240 0.81609 2.7142 268.893 aecbgc
aecbhc 0.1 12 240 10 12 240 0.84201 2.77 267.87 aecbhc
aedbad 0.1 12 270 10 5 270 0.56808 2.2398 280.444 aedbad
aedbbd 0.1 12 270 10 6 270 0.70011 2.5122 284.147 aedbbd
aedbcd 0.1 12 270 10 7 270 0.78768 2.653 285.41 aedbcd
aedbdd 0.1 12 270 10 8 270 0.85049 2.7619 285.554 aedbdd
aedbed 0.1 12 270 10 9 270 0.90241 2.8575 285.366 aedbed
aedbfd 0.1 12 270 10 10 270 0.94697 2.9324 284.802 aedbfd
aedbgd 0.1 12 270 10 11 270 0.98354 2.9943 284.008 aedbgd
aedbhd 0.1 12 270 10 12 270 1.01283 3.0471 283.412 aedbhd
aeebae 0.1 12 300 10 5 300 0.60179 2.3396 291.267 aeebae
aeebbe 0.1 12 300 10 6 300 0.76498 2.654 295.855 aeebbe
aeebce 0.1 12 300 10 7 300 0.87336 2.836 298.847 aeebce
aeebde 0.1 12 300 10 8 300 0.94711 2.953 300.802 aeebde
aeebee 0.1 12 300 10 9 300 1.00434 3.0372 302.172 aeebee
aeebfe 0.1 12 300 10 10 300 1.04743 3.1011 303.12 aeebfe
aeebge 0.1 12 300 10 11 300 1.08744 3.1592 303.937 aeebge
aeebhe 0.1 12 300 10 12 300 1.11473 3.2013 304.334 aeebhe
aefbaf 0.1 12 330 10 5 330 0.59373 2.2891 304.602 aefbaf
aefbbf 0.1 12 330 10 6 330 0.77081 2.6361 308.043 aefbbf
aefbcf 0.1 12 330 10 7 330 0.8916 2.8615 310.959 aefbcf
aefbdf 0.1 12 330 10 8 330 0.97626 3.003 313.037 aefbdf
aefbef 0.1 12 330 10 9 330 1.04778 3.1053 314.568 aefbef
aefbff 0.1 12 330 10 10 330 1.09722 3.1947 315.922 aefbff
aefbgf 0.1 12 330 10 11 330 1.14096 3.2635 316.883 aefbgf
aefbhf 0.1 12 330 10 12 330 1.17653 3.3093 317.866 aefbhf
aegbag 0.1 12 360 10 5 360 0.53003 2.0859 327.098 aegbag
aegbbg 0.1 12 360 10 6 360 0.67865 2.4057 326.103 aegbbg
aegbcg 0.1 12 360 10 7 360 0.77606 2.605 325.267 aegbcg
aegbdg 0.1 12 360 10 8 360 0.84569 2.7497 325.699 aegbdg
aegbeg 0.1 12 360 10 9 360 0.90479 2.8624 326.157 aegbeg
aegbfg 0.1 12 360 10 10 360 0.94855 2.9547 326.572 aegbfg
aegbgg 0.1 12 360 10 11 360 0.98412 3.0258 327.209 aegbgg
aegbhg 0.1 12 360 10 12 360 1.01361 3.0871 327.709 aegbhg
baabaa 1 4 180 10 5 180 0.24058 1.2757 199.398 baabaa
baabba 1 4 180 10 6 180 0.27923 1.3821 208.208 baabba
baabca 1 4 180 10 7 180 0.31422 1.4609 215.106 baabca
baabda 1 4 180 10 8 180 0.34513 1.5313 219.194 baabda
baabea 1 4 180 10 9 180 0.37023 1.6075 221.643 baabea
baabfa 1 4 180 10 10 180 0.39398 1.6783 222.965 baabfa
baabga 1 4 180 10 11 180 0.4092 1.7249 224.609 baabga
baabha 1 4 180 10 12 180 0.41869 1.7784 227.956 baabha
babbab 1 4 210 10 5 210 0.39006 1.7964 258.65 babbab
babbbb 1 4 210 10 6 210 0.46528 2.0025 261.856 babbbb
babbcb 1 4 210 10 7 210 0.51951 2.146 262.599 babbcb
babbdb 1 4 210 10 8 210 0.56225 2.2685 262.472 babbdb
babbeb 1 4 210 10 9 210 0.59937 2.3782 262.117 babbeb
babbfb 1 4 210 10 10 210 0.63753 2.4683 261.097 babbfb
babbgb 1 4 210 10 11 210 0.66614 2.5456 260.781 babbgb
babbhb 1 4 210 10 12 210 0.69358 2.6128 260.49 babbhb
bacbac 1 4 240 10 5 240 0.49894 2.1519 271.641 bacbac
bacbbc 1 4 240 10 6 240 0.61202 2.4392 275.127 bacbbc
bacbcc 1 4 240 10 7 240 0.68738 2.6049 275.734 bacbcc
bacbdc 1 4 240 10 8 240 0.7472 2.7346 275.47 bacbdc
bacbec 1 4 240 10 9 240 0.8002 2.8438 274.822 bacbec
bacbfc 1 4 240 10 10 240 0.86821 2.9233 273.774 bacbfc
bacbgc 1 4 240 10 11 240 0.91111 2.9973 272.853 bacbgc
bacbhc 1 4 240 10 12 240 0.94778 3.0726 272.076 bacbhc
badbad 1 4 270 10 5 270 0.57546 2.4029 280.97 badbad
badbbd 1 4 270 10 6 270 0.72859 2.7443 284.898 badbbd
badbcd 1 4 270 10 7 270 0.83737 2.9329 286.76 badbcd
badbdd 1 4 270 10 8 270 0.9248 3.0464 287.218 badbdd
badbed 1 4 270 10 9 270 0.98738 3.1506 287.046 badbed
badbfd 1 4 270 10 10 270 1.04096 3.232 286.733 badbfd
badbgd 1 4 270 10 11 270 1.08519 3.3006 286.261 badbgd
badbhd 1 4 270 10 12 270 1.12628 3.3572 285.631 badbhd
baebae 1 4 300 10 5 300 0.60997 2.488 290.689 baebae
baebbe 1 4 300 10 6 300 0.79135 2.8787 294.279 baebbe
baebce 1 4 300 10 7 300 0.92284 3.1042 297.309 baebce
baebde 1 4 300 10 8 300 1.01209 3.2539 298.989 baebde
baebee 1 4 300 10 9 300 1.08283 3.3629 299.978 baebee
baebfe 1 4 300 10 10 300 1.1389 3.4494 300.698 baebfe
baebge 1 4 300 10 11 300 1.18855 3.5161 301.208 baebge
baebhe 1 4 300 10 12 300 1.22772 3.5738 301.387 baebhe
bafbaf 1 4 330 10 5 330 0.59511 2.3679 303.532 bafbaf
bafbbf 1 4 330 10 6 330 0.78055 2.751 306.017 bafbbf
bafbcf 1 4 330 10 7 330 0.91062 2.9936 308.585 bafbcf
bafbdf 1 4 330 10 8 330 1.00333 3.1496 310.26 bafbdf
bafbef 1 4 330 10 9 330 1.07643 3.2671 311.691 bafbef
bafbff 1 4 330 10 10 330 1.1327 3.3629 312.804 bafbff
bafbgf 1 4 330 10 11 330 1.17797 3.4338 313.7 bafbgf
bafbhf 1 4 330 10 12 330 1.21634 3.4937 314.503 bafbhf
bagbag 1 4 360 10 5 360 0.53177 2.1029 326.288 bagbag
bagbbg 1 4 360 10 6 360 0.68484 2.4305 325.487 bagbbg
bagbcg 1 4 360 10 7 360 0.78173 2.6281 324.941 bagbcg
bagbdg 1 4 360 10 8 360 0.85132 2.7742 325.147 bagbdg
bagbeg 1 4 360 10 9 360 0.91069 2.8959 325.366 bagbeg
bagbfg 1 4 360 10 10 360 0.96092 2.9839 325.68 bagbfg
bagbgg 1 4 360 10 11 360 0.99889 3.0672 325.877 bagbgg
bagbhg 1 4 360 10 12 360 1.03317 3.1326 326.217 bagbhg
bbabaa 1 6 180 10 5 180 0.25106 1.312 200.246 bbabaa
bbabba 1 6 180 10 6 180 0.29573 1.4185 212.151 bbabba
bbabca 1 6 180 10 7 180 0.33405 1.5133 220.381 bbabca
bbabda 1 6 180 10 8 180 0.36489 1.603 224.25 bbabda
bbabea 1 6 180 10 9 180 0.39165 1.6744 226.457 bbabea
bbabfa 1 6 180 10 10 180 0.41799 1.7527 227.702 bbabfa
bbabga 1 6 180 10 11 180 0.43349 1.8019 229.218 bbabga
bbabha 1 6 180 10 12 180 0.44665 1.8332 230.196 bbabha
bbbbab 1 6 210 10 5 210 0.41037 1.8175 258.228 bbbbab
bbbbbb 1 6 210 10 6 210 0.49575 2.0273 262.038 bbbbbb
bbbbcb 1 6 210 10 7 210 0.55597 2.1808 263.257 bbbbcb
bbbbdb 1 6 210 10 8 210 0.60424 2.2996 263.076 bbbbdb
bbbbeb 1 6 210 10 9 210 0.64388 2.4055 262.594 bbbbeb
bbbbfb 1 6 210 10 10 210 0.68331 2.4974 261.484 bbbbfb
bbbbgb 1 6 210 10 11 210 0.7145 2.5765 261.255 bbbbgb
bbbbhb 1 6 210 10 12 210 0.74184 2.6386 260.942 bbbbhb
bbcbac 1 6 240 10 5 240 0.52266 2.1963 271.985 bbcbac
bbcbbc 1 6 240 10 6 240 0.65307 2.5209 276.374 bbcbbc
bbcbcc 1 6 240 10 7 240 0.74142 2.6973 277.348 bbcbcc
bbcbdc 1 6 240 10 8 240 0.80849 2.8294 277.37 bbcbdc
bbcbec 1 6 240 10 9 240 0.8665 2.9428 276.832 bbcbec
bbcbfc 1 6 240 10 10 240 0.91622 3.0373 275.891 bbcbfc
bbcbgc 1 6 240 10 11 240 0.9602 3.1108 274.932 bbcbgc
bbcbhc 1 6 240 10 12 240 0.99603 3.1787 274.212 bbcbhc
bbdbad 1 6 270 10 5 270 0.5974 2.4549 280.917 bbdbad
bbdbbd 1 6 270 10 6 270 0.76905 2.8427 285.36 bbdbbd
bbdbcd 1 6 270 10 7 270 0.89468 3.0614 287.903 bbdbcd
bbdbdd 1 6 270 10 8 270 0.98267 3.2035 288.692 bbdbdd
bbdbed 1 6 270 10 9 270 1.05279 3.3103 288.798 bbdbed
bbdbfd 1 6 270 10 10 270 1.10809 3.3953 288.486 bbdbfd
bbdbgd 1 6 270 10 11 270 1.15831 3.4701 288.038 bbdbgd
bbdbhd 1 6 270 10 12 270 1.20265 3.5324 287.42 bbdbhd
bbebae 1 6 300 10 5 300 0.63218 2.5135 290.177 bbebae
bbebbe 1 6 300 10 6 300 0.82934 2.945 293.968 bbebbe
bbebce 1 6 300 10 7 300 0.98435 3.1862 297.289 bbebce
bbebde 1 6 300 10 8 300 1.08739 3.3529 299.47 bbebde
bbebee 1 6 300 10 9 300 1.16378 3.453 300.688 bbebee

 

[Lori]

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 insert>Names>Create 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.)

 

[MarkFranklin]

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]

Thanks for the reply and glad it helped.

For the second part, it should be F2 for the waterlvl lookup value, try this
instead. (i entered this in the cell below, F3, in my test).

You might want to try this in a simple interpolation example without the
extra criteria to see how it works. e.g. se
http://groups.google.com/group/micr...ae28430d747/bf177508048be66d#bf177508048be66d

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

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 insert>Names>Create 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.)