+ Reply to Thread
Results 1 to 10 of 10

INDEX-MATCH: Dealing with missing lookup values

  1. #1
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    INDEX-MATCH: Dealing with missing lookup values

    I've been working diligently on this reports spreadsheet and I'm almost there, but I've run into a snag with the following (admittedly long & clunky) formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I know, I know, it's a beast, but it's the only way I could figure-out how to deal with changing/shifting items in the first column from day to day. Everything was working great until I figured out that at some point an item would be added mid-month which means on the Daily Reports there would be some days that would not include the full referenced list in the monthly report formula. For example, if I go to any random day and delete one item, I get the dreaded #N/A error on the Monthly Report.

    I've tried incorporating ISERROR in the formula, but that just gives me a value of 0 in the monthly report. What I need is a way for the formula to simply skip the days it doesn't find that particular value and tabulate the rest of the days of the month for that line item.

    Hopefully, I made myself clear. Thanks in advance for any help, even if it's not a full solution. I'm a bit of a hack, but can figure things out if pointed in the right direction.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: INDEX-MATCH: Dealing with missing lookup values

    The whole problem here is the layout you have for Daily (although, to be honest, Im thinking hard on how else this could be laid out)

    Do you need each heading for each day - do the price/cost change daily?
    Is the format/layout/sequence of items always the same each day/month/year?
    Could you add some dummy data in there please, I am thinking that a sumifs() or sumproduct() might work here, need some data in to play with
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: INDEX-MATCH: Dealing with missing lookup values

    Will put some dummy data in there, but in the meanwhile let me say that I started with SUMIF and thought I was some kind of genius.....until I realized that the item list could change every single day of the month. So that's when the monthly formula transformed into a Frankenstein in order to catch each day's possible changes.

    So the answer to your questions:
    1) I do need each heading for every day
    2) Price/cost can change from day to day with no predictable pattern.
    3) The format/layout/sequence does indeed change, perhaps not everyday, but often enough mid-month.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: INDEX-MATCH: Dealing with missing lookup values

    Quote Originally Posted by Big.Moe View Post
    So the answer to your questions:
    1) I do need each heading for every day
    2) Price/cost can change from day to day with no predictable pattern.
    3) The format/layout/sequence does indeed change, perhaps not everyday, but often enough mid-month.
    bummer, figures LOL

    OK, I will wait for some dummy data, but it's getting late here, may have to pick this up in the morning

  5. #5
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: INDEX-MATCH: Dealing with missing lookup values

    No problem. I know this is a tough one, and maybe the answer is that I'm over-complicating things, but I'm trying to dummy proof the spreadsheet since the data entry person is not well versed with Excel. Might just have to simplify things, or rethink layout.

    Anyway, here's some dummy data. You'll see I inserted the line item "Halibut - Box". While playing with the dummy data, I also figured out I have some other formula/layout tweaks to make.....always something isn't it?
    Attached Files Attached Files

  6. #6
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: INDEX-MATCH: Dealing with missing lookup values

    Quote Originally Posted by FDibbins View Post
    The whole problem here is the layout you have for Daily (although, to be honest, Im thinking hard on how else this could be laid out)
    I was just thinking about this comment. Would it be easier if I aligned the Daily Reports vertically instead of horizontally? Haven't thought it through, but that means all the line items would be in one column. In terms of layout I prefer the horizontal orientation, but if there too many hoops to jump through and vertical is easier, then I might have to just bite the bullet.
    Last edited by Big.Moe; 02-17-2017 at 03:37 AM.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: INDEX-MATCH: Dealing with missing lookup values

    Doing a little playing around with the layout of the daily reports you could simplify the formulas* on the monthly reports to read something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *Note: these are array entered formulas which are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Some of the changes that were made on the daily reports sheet are that the item descriptions are removed except for column B, and the freeze panes are set to C8 so that those descriptions are visible when scrolling to the right. The Price/Unit, Cost/Unit and Profit/Unit formulas are tied to column B by =IF($B8="",""...
    There are other cosmetic changes, I only worked with the first few days, on the daily report sheet that convey, hopefully, what I am talking about.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: INDEX-MATCH: Dealing with missing lookup values

    I want to thank both FDibbins & JeteMc for their great input. You guys got me thinking in a different way and my brain started to get traction after spinning for a while.

    I looked really hard at JeteMc's solution because I never thought of freezing the items column on the left - that was genius, and I'll remember that trick for the future. Unfortunately, for this project, doing this created new issues that I could not resolve.

    So then I went back to FDibbins' remarks and looked into using SUMIFS. The SUMIFS didn't work-out, but using a bunch of SUMIF functions did! The formula is still a bit of a hack job, but that's me. A leopard can't change his spots.

    Here's the formula for the record:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I also attached the updated (but still in progress) file in case you wanted to take a peek.

    Again, many thanks to both of you for taking the time to give me some input. Couldn't of done it without you!
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: INDEX-MATCH: Dealing with missing lookup values

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: INDEX-MATCH: Dealing with missing lookup values

    Moe, thanks for the kind words and feedback. Sometimes it takes a fresh pair (s) of eyes to ask a few questions, to get the juices flowing again

    hmm just had a thought...
    Do you really need each day in its own table/columns? (maybe thats what you meant in post #6?) I am thinking that if you had 1 day below the other, and just added a date column, this whole thing would be really simple

    Take a look at the attached. I have repeated 1/1/2017 data, down for a few more days, to show what I mean

    Then on the summary sheet, the sum becomes...
    =SUMIFS('Daily - Jan'!$F:$F,'Daily - Jan'!$B:$B,Monthly!$B8,'Daily - Jan'!$A:$A,">="&DATEVALUE(1&$B$2&$E$2),'Daily - Jan'!$A:$A,">="&EOMONTH(DATEVALUE(1&$B$2&$E$2),0))
    Attached Files Attached Files

+ 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. Sum Multiple Match Index Lookup Values
    By figo12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-15-2016, 06:15 PM
  2. [SOLVED] Using INDEX(MATCH) with multiple lookup values
    By Wdr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-11-2016, 09:33 AM
  3. INDEX/MATCH multiple lookup values
    By mythbit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2015, 08:55 AM
  4. index match for 3 lookup values
    By krazyhype19 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2014, 06:58 AM
  5. Match Index , missing values.
    By termsig in forum Excel General
    Replies: 6
    Last Post: 12-18-2011, 09:39 PM
  6. Lookup 2 possible values within INDEX/MATCH function.
    By Pete123abc in forum Excel General
    Replies: 2
    Last Post: 06-07-2011, 10:51 AM
  7. Plotting Data against times - Dealing with missing values
    By mynci in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-17-2008, 06:49 AM

Tags for this Thread

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