match #n/a problem

[xnman]

I have a range of cells selected. Within that range is some "#N/A" and
numbers. I am wanting to find the row of the largest number in the range.
Thanks in advance.

 

[paul gurdin]

If your error messages appear in cells in column A then
type formula in column be saying =if(isna(A2),0,a2)

If the "N/A" is being caused as a result of a lookup then
change the lookup formula to read =if(isna(vlookup
(A2,lookuprange,columntoreturn,0)),0,vlookup
(A2,lookuprange,columntoreturn,0))

where lookuprange is the table you looked up and
columntoreturn is the column number containing the value
you want to return.

To find the row of the range containing the highest number
then type in =max(range).

Hope this helps.

 

[Peo Sjoblom]

Try something like

=MAX(IF(ISNUMBER(A1:A10),A1:A10))

array entered with ctrl + shift & enter

regards,

Peo Sjoblom