# Need help with an array formula

#### tbablue

##### Active Member
{=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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### xld

##### Banned
=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)))

Replies
4
Views
426
Replies
5
Views
167
Replies
0
Views
754
Replies
1
Views
233
Replies
17
Views
1K

1,190,876
Messages
5,983,355
Members
439,840
Latest member
billy1989

### 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.

### Which adblocker are you using?

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

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