Finding Max # in a list of similar models

**elcarp** · 12-13-2012, 03:14 PM

I have a list of full model #'s and a list of truncated model #'s. What I need a formula to be able to do, is look at the prices for all of the full model #'s and return the highest value as the price in the cell adjacent to the truncated model #. An example file of what I need is attached.

example.xlsx

Thanks in advance for your help.

**tigeravatar** · 12-13-2012, 03:27 PM

elcarp,

Attached is a modified version of your posted workbook.
In cell B2 and copied down is this formula:

=MAX(INDEX(ISNUMBER(SEARCH(A2,$G$2:$G$11))*$H$2:$H$11,))

**Pete_UK** · 12-13-2012, 03:29 PM

Put this array* formula in B2:

=MAX(IF(LEFT($G$2:$G$11,LEN(A2))=A2,$H$2:$H$11))

then copy down.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter.

Hope this helps.

Pete

**JBeaucaire** · 12-13-2012, 03:31 PM

Put this array formula in B2:

=MAX(IF(LEFT($G$2:$G$11,LEN(A2))=A2, $H$2:$H$11))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array, not just ENTER. Once the first formula is inserted and working, copy that cell downward.

**elcarp** · 12-13-2012, 03:41 PM

Tigeravatar, Pete_UK, and JBeaucaire...

Thank you for your prompt responses. Both solutions work to the job. I will probably use the second suggestion simply because I understand it better, not because it worked better. Thanks all, you just saved me a heap of time.