remove NA result from array formule

[rickf19]

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

 

[steve the fish]

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]

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]

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

 

[rickf19]

Thanks again