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
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
7,951
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,951
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,507
Messages
5,414,961
Members
403,557
Latest member
hsstrider

This Week's Hot Topics

Top