remove NA result from array formule

rickf19

Board Regular
Joined
Aug 30, 2019
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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)
 
Upvote 0
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
 
Upvote 0
You could use both of those IFERRORs combined in one formula but its always good to see errors if they may be there unexpectedly.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top