# Ignore #N/A in Array Formula

#### r1998

##### Board Regular
Dear Friends and Respected Seniors,
I have this data in A1-B10
 1 28 2 20 3 80 3 70 2 65 1 40 2 88 1 33 3 41 2 58

<tbody>
</tbody>

I want to find the maximum value from column B wherever column A has value 1, so in C1 I am using
Code:
``=MAX(IF(A:A=A1,B:B))``
with control+shift+enter
I am getting the correct answer as 40.
But if column B has any #N/A values, I get #N/A in C1, that is, if I change B8 from 33 to #N/A, I get #N/A in C1.
How can I ignore #N/A values in above formula.
Can anyone please kindly help me.
Thank you.

Last edited:

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Marcelo Branco

##### MrExcel MVP
First of all, for the sake of performance avoid references to entire columns in array formulas, so try something like this

=MAX(IF(A1:A100=A1,IF(ISNUMBER(B1:B100),B1:B100)))
Ctrl+Shift+Enter

M.

#### r1998

##### Board Regular
Thank you Marcelo Branco Sir,
Henceforth, I will not use reference of entire columns
Thank you.

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Glad to help

M

Replies
6
Views
39
Replies
6
Views
36
Replies
9
Views
100
Replies
5
Views
60
Replies
5
Views
71