remove NA result from array formule

rickf19

New Member
Joined
Aug 30, 2019
Messages
14
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,818
Office Version
365
Platform
Windows
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
Joined
Aug 30, 2019
Messages
14
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
Joined
Oct 20, 2009
Messages
7,818
Office Version
365
Platform
Windows
You could use both of those IFERRORs combined in one formula but its always good to see errors if they may be there unexpectedly.
 

Forum statistics

Threads
1,084,984
Messages
5,381,022
Members
401,712
Latest member
Aubin

Some videos you may like

This Week's Hot Topics

Top