VLOOKUP, MAX, IF, ARRAY combination problem

S

Sayeem

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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Hi,

Check if this works for you:


Book1
IJKL
18X-Acc+g (m/s^2)Y-Acc+g (m/s^2)Z-Acc (m/s^2)
19Package 10,690,630,96
20Package 11+140,540,622,82
Sheet1
Cell Formulas
RangeFormula
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))
 
Upvote 0
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
Code: 
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.
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
formula to return two columns of data adjacent to #N/A cell entry
Replies
3
Views
377
Fluff
Fluff
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
277
MeaclH
M
F
  • Solved
Complex Dynamic Array Cash Flow Spread - Returns Only $0
2
Replies
16
Views
637
felixstraube
F
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
611
Greenbehindthecells
G
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top