Max of a Single-Cell Array
 

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?

Max of a Single-Cell Array
 

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)


--
Biff
Microsoft Excel MVP


"ajd" wrote in message
...
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?