# remove NA result from array formule

#### rickf19

##### New Member
Hi all

I have an array lookup formula which returns a number of NA results, I know why they are there but as others view the spreadsheet as part of a pack I would appreciate it if there was a way to put something else on the sheet similair to the if isna function on a normal lookup.
formula is shown below
{=SUM(VLOOKUP(\$D4,Jan!\$E\$6:\$AL\$250,{4,7,8,9,18,25,26,28,29},FALSE))}

any help greatly appreciated

Thanks
Rick

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### steve the fish

##### Well-known Member
If the Jan sheet has errors in the target columns then it will error. The below would sort that:

=SUM(IFERROR(VLOOKUP(\$D4,Jan!\$E\$6:\$AL\$250,{4,7,8,9,18,25,26,28,29},FALSE),0))

If the error is caused by the lookup value not being found in Jan!E6:E250 then maybe just:

=IFERROR(SUM(VLOOKUP(\$D4,Jan!\$E\$6:\$AL\$250,{4,7,8,9,18,25,26,28,29},FALSE),0),0)

#### rickf19

##### New Member
Thanks so much you have no idea how much time I have spent trying to sort this.
Target column D in the formula returns error cos it doesnt exist on look up range sheet but thats fine as it can appear at any time throughout the year
Have already noted both answers in my dont forget excel tips notebook
Thanks
Rick

#### steve the fish

##### Well-known Member
You could use both of those IFERRORs combined in one formula but its always good to see errors if they may be there unexpectedly.

Thanks again