Results 1 to 6 of 6

Thread: Return value if there is an #N/A error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Return value if there is an #N/A error

    Okay so I have a list of numbers that will be pasted next to an already placed VLOOKUP function, this VLOOKUP will lookup the numbers pasted in another list. This means the VLOOKUP returns a list of all the numbers that already exist in the 'lookup list' and then #N/A if a new number is there. I want a formula that returns those new numbers... for example

    'Lookup list' Pasted VLOOKUP New (formula I'm looking for)
    #245 #245 #245 #233
    #267 #233 #N/A #288
    #244 #267 #267
    #235 #244 #244
    #266 #288 #N/A
    #235 #235
    #266 #266

    Thanks,
    Matt

  2. #2
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return value if there is an #N/A error

    'Lookup list' Pasted VLOOKUP New (formula I'm looking for)
    #245#245#245#233
    #267#233 #N/A #288
    #244 #267#267
    #235 #244#244
    #266 #288 #N/A
    #235#235
    #266#266

    The spaces aren't showing in the original query

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Re: Return value if there is an #N/A error

    'Lookup list' Pasted VLOOKUP New (formula needed)
    #245 #245 #245 #233
    #267 #233 #N/A #288
    #244 #267 #267
    #235 #244 #244
    #266 #288 #N/A
    #235 #235
    #266 #266
    Apologies, should have done a table to start.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Return value if there is an #N/A error

    Try this, copied down.

    new numbers

    ABCD
    1
    2#245#245#245#233
    3#267#233#N/A#288
    4#244#267#267
    5#235#244#244
    6#266#288#N/A
    7 #235#235
    8 #266#266
    9

    Spreadsheet Formulas
    CellFormula
    D2=IFERROR(INDEX(B$2:B$10,AGGREGATE(15,6,(ROW(B$2:B$10)-ROW(B$2)+1)/ISNA(C$2:C$10),ROWS(D$2:D2))),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return value if there is an #N/A error

    That is amazing! Thank you

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Return value if there is an #N/A error

    Quote Originally Posted by mattbnorris View Post
    That is amazing! Thank you
    Glad it worked for you. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •