Lookup Help!!

[cdoc83]

Hi all, I really hope you can help. I have the following spreadsheet that I wish to look up specific values.
</pre>

---

The raw data is below. For example I want to look up 1.5 Hs and 60deg Direction and return the value for X, Y and Z. Cheers in advance for any help.
</pre>

---

		Stern Seas							Port Seas							Bow Seas							Starboard Seas
Direction		0	30	60	90	120	150	180	210	240	270	300	330	All
		X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z	X	Y	Z
Hs	0.5	0.01	0.00	1.01	0.01	0.02	1.02	0.01	0.04	1.02	0.00	0.04	1.05	0.01	0.04	1.03	0.01	0.03	1.02	0.01	0.00	1.01	0.01	0.03	1.02	0.01	0.04	1.03	0.00	0.04	1.05	0.01	0.04	1.02	0.00	0.02	1.01	0.01	0.04	1.05
1	0.01	0.00	1.03	0.01	0.05	1.03	0.01	0.08	1.04	0.01	0.09	1.09	0.02	0.08	1.05	0.01	0.05	1.03	0.01	0.00	1.03	0.01	0.05	1.03	0.02	0.08	1.05	0.01	0.08	1.10	0.01	0.07	1.04	0.01	0.04	1.03	0.02	0.09	1.10
1.5	0.02	0.00	1.04	0.02	0.07	1.05	0.02	0.12	1.07	0.01	0.13	1.14	0.03	0.13	1.08	0.02	0.08	1.05	0.02	0.00	1.04	0.02	0.08	1.05	0.03	0.12	1.08	0.01	0.12	1.15	0.02	0.11	1.07	0.01	0.07	1.04	0.03	0.13	1.15
2	0.02	0.00	1.06	0.02	0.09	1.06	0.03	0.16	1.09	0.01	0.18	1.19	0.04	0.17	1.10	0.03	0.10	1.06	0.03	0.00	1.06	0.03	0.10	1.06	0.04	0.16	1.10	0.01	0.16	1.20	0.03	0.14	1.09	0.02	0.09	1.05	0.04	0.18	1.20
3	0.03	0.00	1.09	0.04	0.14	1.10	0.04	0.24	1.13	0.02	0.27	1.28	0.06	0.26	1.15	0.04	0.16	1.10	0.04	0.00	1.09	0.04	0.15	1.09	0.06	0.23	1.15	0.02	0.24	1.30	0.04	0.21	1.13	0.03	0.13	1.08	0.06	0.27	1.30
4	0.04	0.00	1.11	0.05	0.20	1.13	0.05	0.32	1.18	0.02	0.37	1.38	0.07	0.35	1.21	0.05	0.21	1.13	0.05	0.00	1.12	0.05	0.19	1.12	0.07	0.30	1.21	0.02	0.32	1.40	0.05	0.28	1.18	0.04	0.16	1.11	0.07	0.37	1.40
5	0.05	0.00	1.14	0.06	0.25	1.16	0.07	0.41	1.22	0.03	0.47	1.47	0.09	0.44	1.26	0.07	0.27	1.16	0.06	0.00	1.14	0.07	0.24	1.16	0.09	0.37	1.26	0.03	0.39	1.50	0.07	0.34	1.22	0.05	0.20	1.14	0.09	0.47	1.50
6	0.06	0.00	1.17	0.07	0.31	1.19	0.08	0.50	1.26	0.03	0.57	1.56	0.11	0.53	1.31	0.08	0.33	1.19	0.07	0.00	1.18	0.08	0.28	1.19	0.11	0.44	1.31	0.03	0.46	1.56	0.08	0.40	1.26	0.06	0.23	1.16	0.11	0.57	1.56
7	0.07	0.00	1.20	0.09	0.36	1.23	0.09	0.59	1.31	0.04	0.67	1.66	0.12	0.62	1.36	0.09	0.39	1.22	0.09	0.00	1.21	0.09	0.32	1.22	0.12	0.51	1.32	0.04	0.53	1.66	0.09	0.45	1.31	0.07	0.27	1.19	0.12	0.67	1.66
8	0.08	0.00	1.23	0.10	0.42	1.26	0.10	0.68	1.36	0.01	0.76	1.53	0.11	0.72	1.41	0.11	0.45	1.23	0.10	0.00	1.20	0.11	0.36	1.23	0.11	0.58	1.36	0.01	0.59	1.49	0.10	0.50	1.35	0.08	0.30	1.22	0.11	0.76	1.53
9	0.08	0.00	1.26	0.11	0.48	1.29	0.12	0.77	1.44	0.03	0.86	1.84	0.16	0.81	1.45	0.12	0.51	1.28	0.11	0.00	1.28	0.12	0.40	1.28	0.16	0.65	1.41	0.03	0.66	1.84	0.12	0.55	1.39	0.09	0.33	1.25	0.16	0.86	1.84
10	0.10	0.00	1.28	0.12	0.54	1.32	0.17	0.85	1.49	0.04	0.96	1.94	0.21	0.89	1.51	0.14	0.56	1.31	0.12	0.00	1.31	0.14	0.44	1.32	0.21	0.71	1.47	0.04	0.72	1.94	0.17	0.60	1.44	0.11	0.37	1.28	0.21	0.96	1.94

<colgroup><col><col><col span="8"><col><col span="30"></colgroup><tbody>
</tbody>

</pre>

 

[DILIPandey]

It can be done using Index, Match nesting. Can you share the column name , row number from where you data starts ?


Regards,
DILIPandey

 

[cdoc83]

It can be done using Index, Match nesting. Can you share the column name , row number from where you data starts ?


Regards,
DILIPandey

Hi thank you for your reply.

The cell references are as follows - 0.5 is in cell C7, 1 is in cell C8 and so on.

I have tried to attach the sheet but not sure how to do it.

Thanks again.

 

[DILIPandey]

Okay.. use below formula:-

=INDEX($C$5:$AP$17,MATCH($C$21,$C$5:$C$17,0),MATCH($D$21,$C$4:$AP$4,0)+COLUMN(A$1)-1)


Regards,
DILIPandey