Need help with an array formula

tbablue

Active Member
Joined
Apr 29, 2007
Messages
482
Office Version
  1. 365
Platform
  1. Windows
{=MAX(IF('[LONG RANGE RESOURCE PLANNER.xls]Resource - DESIGN'!$I$10:$I$870=$B8,IF(ISNUMBER(MATCH('[LONG RANGE RESOURCE PLANNER.xls]Resource - DESIGN'!$M$10:$M$870,{"INV","COMPILE","MP CHECK","MP RE-WRK"},0)),'[LONG RANGE RESOURCE PLANNER.xls]Resource - DESIGN'!$P$10:$P$870)))}

This array formula checks for a unique identifier ~
('[LONG RANGE RESOURCE PLANNER.xls]Resource - DESIGN'!$I$10:$I$870)

Checks if the following text is found ~
MATCH('[LONG RANGE RESOURCE PLANNER.xls]Resource - DESIGN'!$M$10:$M$870,{"INV","COMPILE","MP CHECK","MP RE-WRK"},0))

and returns a corresponding value that meets the criteria ~
'[LONG RANGE RESOURCE PLANNER.xls]Resource - DESIGN'!$P$10:$P$870)

This formula works perfectly at returning the highest value numeric value from column P

Now however, in the cell adjacent I'd like to return the value found in ~
'[LONG RANGE RESOURCE PLANNER.xls]Resource - DESIGN'!$H$10:$H$870) instead

I've tried amending the formula to suit but cant make it work. I think it's because the original array was returning a numeric value (so MAX was wholly applicable) but my new requirement (column H) is a name, a text string.

Can anyone help me? I think SUMPRODUCT may be needed but dont understand how to employ it.

Any assistance welcome.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
=index($q$10:$q$870,min(if($p$10:$p$870=max(if($i$10:$i$870=$b8,
If(isnumber(match($m$10:$m$870,{"inv","compile","mp Check","mp Re-wrk"},0)),$p$10:$p$870))),row($p$10:$p$870)-row($p$10)+1)))
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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