+ Reply to Thread
Results 1 to 5 of 5

Using two Vlookups to subtract, how to show "No Data" if Neither exist

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    22

    Using two Vlookups to subtract, how to show "No Data" if Neither exist

    I have a table divided by months with rows of data. I'm currently using this formula to subtract two different months from each other of a specific item.

    =IF(ISNUMBER(MATCH(CONCATENATE($B6,$B$2,D$5),All_Data!$D:$D,0)),LOOKUP(CONCATENATE($B6,$B$2,D$5),All_Data!$D:$D,All_Data!$F:$F),0)-IF(ISNUMBER(MATCH(CONCATENATE($B6,$B$2,D$32),All_Data!$D:$D,0)),LOOKUP(CONCATENATE($B6,$B$2,D$32),All_Data!$D:$D,All_Data!$F:$F),0)

    What I want to do is if neither of those find a number to have it say "No Data." I currently have it set to show 0 if the value doesn't exist in each lookup. The reason is because if one of the values still can be looked up, I need to still subtract it from 0.
    I need a way to set it up if NEITHER value exist that it'll say "No Data"

    Thank you.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,974

    Re: Using two Vlookups to subtract, how to show "No Data" if Neither exist

    =IFERROR(LOOKUP(CONCATENATE($B6,$B$2,D$5),All_Data!$D:$D,All_Data!$F:$F)-LOOKUP(CONCATENATE($B6,$B$2,D$32),All_Data!$D:$D,All_Data!$F:$F),"No Data")

    or just

    =IFERROR(LOOKUP($B6&$B$2&D$5,All_Data!$D:$D,All_Data!$F:$F)-LOOKUP($B6&$B$2&D$32,All_Data!$D:$D,All_Data!$F:$F),"No Data")

    (I don't like CONCATENATE )

    Also, not sure exactly what you mean by 'if neither exist' - I thook it to mean 'if either or both don't exist' rather than 'if both don't exist'

    If you want a return when one exists but not the other, then we'll need to adjust the formula.
    Last edited by Bernie Deitrick; 11-30-2018 at 03:24 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    06-28-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    22

    Re: Using two Vlookups to subtract, how to show "No Data" if Neither exist

    Thank you for your reply. I mean it as "if both don't exist." I still want it to give me a 0 in the lookup if one exist and one doesn't so it will still subtract. But if neither exist then to say no data.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,974

    Re: Using two Vlookups to subtract, how to show "No Data" if Neither exist

    =IF(AND(ISERROR(MATCH($B6&$B$2&D$5,All_Data!$D:$D,FALSE)),ISERROR(MATCH($B6&$B$2&D$32,All_Data!$D:$D,FALSE))),"No Data",IFERROR(VLOOKUP($B6&$B$2&D$5,All_Data!$D:$F,3,FALSE),0)-IFERROR(VLOOKUP($B6&$B$2&D$32,All_Data!$D:$F,3,FALSE),0))

  5. #5
    Registered User
    Join Date
    06-28-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    22

    Re: Using two Vlookups to subtract, how to show "No Data" if Neither exist

    Thank you! That worked. I appreciate your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cross Reference / "Syncronizing" / "loopy" VLOOKUPs...
    By JUNKdeLUXE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-13-2014, 08:17 AM
  2. If "6343" in cell A3 matches "monthly" in B3 show a "Y" in C3
    By laaxy in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-05-2014, 07:38 PM
  3. Replies: 3
    Last Post: 01-02-2014, 02:15 PM
  4. [SOLVED] Excel 2007: How to Convert "5/2/2013" to "May" then subtract a Month so it's "Apr"
    By Golom in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-19-2013, 02:00 AM
  5. Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" not working
    By redders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 03:52 PM
  6. Rearrange format with "TRIM" and "SUBTRACT" functions
    By Vittorio in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2011, 07:37 PM
  7. Replies: 2
    Last Post: 01-26-2011, 06:45 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1