Results 1 to 5 of 5

Thread: remove NA result from array formule
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Location
    Scotland
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default remove NA result from array formule

    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

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: remove NA result from array formule

    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)
    Looking for opportunities

  3. #3
    New Member
    Join Date
    Aug 2019
    Location
    Scotland
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: remove NA result from array formule

    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

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: remove NA result from array formule

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

  5. #5
    New Member
    Join Date
    Aug 2019
    Location
    Scotland
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: remove NA result from array formule

    Thanks again

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •