VLOOKUP, MAX, IF, ARRAY combination problem

[Sayeem]

Dear All

My data set is given below.

I want to get the maximum value for "Package 1" highlighted in RED, BLUE and the middle column is just BLACK. So the output should be 0.69, 0.59,0.96. There are other packages too. The next one is Package 11+14, for that the answer I want should be 0.54, 0.62, 2.82.

What should be the approach here? See the attached picture to know the row or column numbers.
Picture link - https://drive.google.com/open?id=12KxBezduG3afHeFzb2vIC55w0oLnCAmX

Dataset below:

Basic	Speed 0.0 Knots
	Heading 60	Sea state	Surge Motion (m)	Sway Motion (m)	Heave Motion (m)	Roll Motion (deg)	Pitch Motion (deg)	Yaw Motion (deg)	X-Acc (deg/s^2)	Y-Acc (deg/s^2)	Z-Acc (deg/s^2)	X-Acc+g (m/s^2)	Y-Acc+g (m/s^2)	Z-Acc (m/s^2)
		1.00	1.33	2.32	3.09	12.00	4.78	1.53	7.14	2.57	0.88	0.30	1.31	1.50
		2.00	1.70	2.82	3.70	11.91	5.11	1.65	6.59	2.47	0.83	0.29	1.20	1.53
		3.00	2.00	3.22	4.20	11.45	5.15	1.70	6.00	2.28	0.76	0.27	1.08	1.50
		4.00	2.23	3.52	4.58	10.73	5.02	1.69	5.37	2.06	0.69	0.25	0.96	1.44
		5.00	2.41	3.75	4.87	9.94	4.78	1.65	4.79	1.85	0.62	0.23	0.85	1.37
	Heading 75	Sea state	Surge Motion (m)	Sway Motion (m)	Heave Motion (m)	Roll Motion (deg)	Pitch Motion (deg)	Yaw Motion (deg)	X-Acc (deg/s^2)	Y-Acc (deg/s^2)	Z-Acc (deg/s^2)	X-Acc+g (m/s^2)	Y-Acc+g (m/s^2)	Z-Acc (m/s^2)
		1.00	1.13	2.82	3.77	14.90	4.26	1.46	9.09	2.45	0.90	0.27	1.64	1.90
		2.00	1.39	3.36	4.37	14.64	4.42	1.53	8.34	2.29	0.82	0.25	1.49	1.90
		3.00	1.60	3.78	4.81	13.98	4.35	1.54	7.57	2.07	0.74	0.23	1.34	1.83
		4.00	1.75	4.08	5.13	13.02	4.16	1.51	6.77	1.85	0.66	0.21	1.19	1.72
		5.00	1.86	4.31	5.35	12.01	3.91	1.46	6.03	1.64	0.59	0.19	1.05	1.61
Package 1	Speed 0.0 Knots
	Heading -180	Sea state	X-Acc+g (m/s^2)	Y-Acc+g (m/s^2)	Z-Acc (m/s^2)
		1.00	0.63	0.47	0.75
		2.00	0.67	0.44	0.82
		3.00	0.67	0.40	0.87
		4.00	0.64	0.36	0.89
		5.00	0.60	0.32	0.89
	Heading -165	Sea state	X-Acc+g (m/s^2)	Y-Acc+g (m/s^2)	Z-Acc (m/s^2)
		1.00	0.65	0.63	0.84
		2.00	0.69	0.59	0.91
		3.00	0.68	0.54	0.95
		4.00	0.65	0.49	0.96
		5.00	0.61	0.44	0.95
Package 11+14	Speed 0.0 Knots
	Heading -180	Sea state	X-Acc+g (m/s^2)	Y-Acc+g (m/s^2)	Z-Acc (m/s^2)
		1.00	0.50	0.45	2.42
		2.00	0.54	0.44	2.57
		3.00	0.54	0.42	2.57
		4.00	0.51	0.39	2.47
		5.00	0.48	0.36	2.33
	Heading -165	Sea state	X-Acc+g (m/s^2)	Y-Acc+g (m/s^2)	Z-Acc (m/s^2)
		1.00	0.51	0.62	2.69
		2.00	0.54	0.60	2.82
		3.00	0.54	0.57	2.78
		4.00	0.52	0.52	2.66
		5.00	0.48	0.47	2.49

<tbody>
</tbody>

 

[jorismoerings]

Hi,

Check if this works for you:

Book1
	I	J	K	L
18		X-Acc+g (m/s^2)	Y-Acc+g (m/s^2)	Z-Acc (m/s^2)
19	Package 1	0,69	0,63	0,96
20	Package 11+14	0,54	0,62	2,82
Sheet1

Cell Formulas
Range	Formula
J19	=MAX(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($I19,$A:$A,0),1)),3,3,11,3),0,1))
K19	=MAX(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($I19,$A:$A,0),1)),3,3,11,3),0,2))
L19	=MAX(INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($I19,$A:$A,0),1)),3,3,11,3),0,3))

 

[Sayeem]

Dear Jorismoerings

Do not know how to thank you properly. The formula works based on what I have share before.

However as I try to tweak it to suit my need I initially got 0.00 or #NAME error.

Here is the additional corrections I did to make it work for my actual worksheet.

A. The OFFSET table height is 153 rows so I Corrected height in OFFSET formula.
B. My reference data is in another sheet within same workbook titled "DataHs6.5"
So I changed the ADDRESS formula

ADDRESS(MATCH($H6,DataHs6.5!$A:$A,0),1,,,"DataHs6.5")

Sheet Name - DataHs6.5

Otherwise the INDIRECT formula was not working assuming the reference data is on the same sheet.

Thanks again. I will be back for more help.

 

[jorismoerings]

Hi,

My formula was based on the data provided but if you've made the changes and they work, it's shows you're grasping the construct of the formula.
Well Done!