Max of a Single-Cell Array

[ajd]

I have a list of IDs that I'm performing a vlookup on. But in order to save
space I don't want to list out all of the results of the vlookup. So I did a
single-cell array for the vlookup on the IDs.

{=VLOOKUP(N9:Q9,ModelData!$A:$BZ,63,FALSE)}

That command appears to be working because if I expand the array, all of the
results are there. But then when I do the max() of the single-cell array, it
just returns the first result in the array. What am I doing wrong?

 

[T. Valko]

Try this array formula** :

=MAX(IF(ISNUMBER(MATCH(ModelData!A1:A10,N9:Q9,0)),ModelData!BK1:BK10))

Adjust ranges to suit but note that you can't use entire column references
unless you're using Excel 2007.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)