+ Reply to Thread
Results 1 to 25 of 25

Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

  1. #1
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Angry Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Hello,

    I'm Pete. I love Excel. I impress myself sometimes with the things I can make it do. Then I come here and behold the knowledge that is light-years ahead of mine and want to curl into the fetal position and sob. Alas, I must press forward!

    I've overreached my Excel grasp with this latest challenge, but I don't think it is all that complicated, albeit a bit complex. I hope to soon find out.

    I'll start with a simple TLDR version then get more detailed in the message.

    In short, I would like to create a workbook with two tabs. One tab is where I would use a table's rows to list line items with various columns that contain specifics about those line items. The second tab would be a formatted "invoice" that I would export as a PDF to "bill" someone for services rendered. Basically I enter stuff in Tab #1 of a workbook, using a table to keep track of bits of info about the stuff. Tab #2 would populate automatically with SOME of the info from Tab #1.

    Now for a twist. In Tab #2 (the "Invoice" Tab) I want a pull-down menu that allows me to select from a list of categories. Once I select it, only lines of that category are imported from Tab #1 and placed in Tab #2. Here's an uncomplicated example...

    In Tab #1, I'd use a table to enter some information. For this example, I'll use four columns. (A) Date, (B) Type, (C) Item, (D) Description. I'd populate four rows as follows...

    Row 1: Date: 12/1/16, Type: Fruit, Item: Banana, Description: Yellow no bruises.
    Row 2: Date: 12/2/16, Type: Vegetable, Item: Kale, Description: Green not wilted.
    Row 3: Date: 12/1/16, Type: Dairy, Item: Cheese, Description: Gruyere 8oz.
    Row 4: Date 11/28/16, Type: Fruit, Item: Apple, Description: Green Granny Smith.

    That part is easy and I've already built it. Now, I want to switch to Tab #2, select "Fruit" from a pull-down menu (I know how to do that part!) then have Excel automate the task of making a LIST of the Date and Description. If I change the pull-down menu from "Fruit" to "Dairy" I want to see the table change to list only Dairy from Tab #1. I want to the list to have no empty rows and scale small or large depending on the number of entries.

    I hope my description is simple. I have a sample file I have been trying to make this happen with, and I just can't seem to do it. Every time I choose or settle on a formula, it seems there is a roadblock of some kind that sends me back to the drawing board.

    Well, I am sorry if this was too lengthy, but I hope the communication was sufficient to help you visualize my issue if you're reading this. Thanks in advance if you can think of a way to make this happen using ordinary formulas.

    Sincerely,

    Pete
    Attached Files Attached Files
    Last edited by petelozzi; 12-01-2016 at 08:23 PM. Reason: Adding attachment

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Welcome to Excel Forum.
    This proposed solution employs three array entered formulas* similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    * Array entered formulas 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.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Extraordinary. I am trying to digest this now, and apply it to my bigger scheme. It functions exactly as I had hoped, so I intend to use what you've given me to make my project work! Thank you very much. I wish there was something I could do in return!

  4. #4
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Errr, ok I am trying to map this. Not just using copy/paste but truly trying to understand so I can apply this knowledge in the future. Here's where I am stuck a little bit... In the formula you provided, there is a reference to ROW(A$1:A$4) - why? This seems to be an odd part of the sheet to reference. There is no data in those cells, in fact they are merely there for spacing, yet this formula relies on them to work properly. ::Scratching head::

  5. #5
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    I'm left-handed so that might explain why I am still stuck on this!

    I got the CTRL+SHIFT+ENTER to activate the array situation. I see my brackets and all that...

    Just because, what's going on here?

    =IFERROR(INDEX(Table1[Date],SMALL(IF(Table1[Type]=B$1,ROW(A$1:A$4),""),ROW(A1))),"")

    (Table1[Date], - this tells Excel to return the value in the Date column in Table 1. Got it.
    (If(Table1[Type]=B$1 - this tells Excel to look at B1 and use that as the criteria to match the value, so we know we got the right category. Got it.
    ROW(A$1:A$4),"" - This seems to say, "If yes, return something in rows A1-A4, if no, give me the "" blank space. But, there's nothing in A1-A4 in either tab, that's where I am stuck.
    ROW(A1) - What is this saying to Excel? I have seen this reference before. I don't understand the command.

    Thanks again...

    Pete

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    One good way to see what the formula is doing is to select Tab 2 cell A6 and then run Evaluate Formula from the Formula tab on the ribbon across the top of the spreadsheet. You'll notice that when the formula gets to the ROW(A$1:A$4) portion is makes an array of values {1,2,3,4} The formula will next use that array along with the array of TRUE/FALSE values to show the rows that have 'Fruit' in Tab 1 column B (assuming 'Fruit' is in Tab 2 cell B1). Notice this creates the array {1,"","",4} The SMALL function will now pick the second smallest value, ignoring the blanks, or 4 meaning that INDEX will look in the table [Date] and find the date in row four of that table.
    This means that if your actual data has say 500 lines you'll need to change to ROW(A$1:A$500) Hope this makes sense.
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    You'll notice that when the formula gets to the ROW(A$1:A$4) portion is makes an array of values {1,2,3,4}. The formula will next use that array along with the array of TRUE/FALSE values to show the rows that have 'Fruit' in Tab 1 column B (assuming 'Fruit' is in Tab 2 cell B1). Notice this creates the array {1,"","",4}.
    Ah yes, ok... So this formula assigns "numbers" to the rows then excludes anything that doesn't match the value of B1, giving the {1,"","",4} or otherwise stating, "Rows 1 and 4 match, so we will use those."

    The SMALL function will now pick the second smallest value, ignoring the blanks, or 4 meaning that INDEX will look in the table [Date] and find the date in row four of that table.
    Ah yes the fog is clearing.

    This means that if your actual data has say 500 lines you'll need to change to ROW(A$1:A$500) Hope this makes sense.
    Ok yes this made sense. So, I opened a fresh copy of the workbook that you attached with the correct formula, then tried the following... (My attempt attached for your review...)

    Step 1, expanded the table (by dragging the handle in the lower right corner) on Tab 1 to 10 Rows then filled in more data with various additional info. This modification made it so that table now occupied A1:D10 on Tab 1.

    Step 2, selected Tab 2 with the intention of starting with cell A5 and modifying the formula to reflect a ROW of (A$1:A$10) to replace the original (A$1:A$4). When I got there, I noticed that before I even modified the formula, it was broken. I pulled down the B1 list and selected things but the formula was already not working because of the modifications I made on Tab 1. This struck me as odd, but I assumed that if I change the formulas in A5:A8 (the only needed rows in the original example) to reflect the new range (A$1:A$10) that it should work. It didn't. Why?

    I'm so perplexed because it seems logically doing those two things would be the correct steps, but such odd, unexpected results occur. When I select anything but "Fruit" in the B1 modifier in Tab 2, it just comes up blank. How can this be when I simply extended the formula's range? I am staring at this line of formula and can't see how another variable is making this incorrect??

    {=IFERROR(INDEX(Table1[Date],SMALL(IF(Table1[Type]=B$1,ROW(A$1:A$10),""),ROW(A1))),"")}

    Another baffling side note, when I forgot to use the [CTRL]+[SHIFT]+[ENTER] button combo, I got SOMETHING. When I do use it, I get nothing. Grrrrrrr!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    The reason that the formula yielded nothing is that [Type] has nine values, but the If function is trying to match that with 10 values in the ROW created array. If you change the formula to read =IFERROR(INDEX(Table1[Date],SMALL(IF(Table1[Type]=B$1,ROW(A$1:A$9),""),ROW(A1))),"") you'll get the expected results.

  9. #9
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Wow, I can't tell you how much I appreciate the time you've given me here. I sure hope I get a chance to pay it forward. Many thanks to you.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    The above formula (post #8) can become tedious if you have a dynamic table so here is a variation of the INDEX/SMALL based array formula* that will work without having to use the exact number of rows in the ROW function argument:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Notice: that the ROW function will create an array of 100 elements, which will not match the nine currently in [Type]. However, the IFFERROR function will catch any unmatched elements and return a value of FALSE which SMALL will ignore. Only draw back that I know of is that there will be a bit of extra computing time, but that is probably gong to be negligible (my opinion).
    *Be sure to activate using Ctrl+Shift+Enter.
    Let us know if you have any questions.

  11. #11
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Wow you're clairvoyant too eh? No sooner did that thought pop into my head than I saw the notification that you posted #10! Smashing!

    Now I'm working on getting this engineered with the new knowledge you've provided, thank you!

    I noticed a particular instance where you used the $ symbol in the formula. You used it to "anchor" the B1 reference cell, but only chose to anchor the column and not the row. My thought is that both the row and the column would need to be anchored, like this: $B$1. Is there a reason you chose not to anchor the row? Maybe it was just an easier keystroke not to add the $ to anchor the row, but I can't help but think there might be a good reason for it that I am missing?

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Since the formula is filled down the column only I just 'lock' the row as the column reference won't change. If the formula was going to be also dragged across to other columns then I'd need to 'lock' both the column and row. In this case it wouldn't make a difference in performance if I 'locked' both column and row (my opinion), however I still try to 'lock' only in the direction, down (row) or across (column), needed so that I stay in practice for instances where a formula would only perform properly if it is 'locked' in only one direction. Hope that makes some sort of sense.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Another option for the INDEX/SMALL array formula would be to keep the IF function and reference one of the table columns as the ROW argument as in:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Note: Here one must be subtracted from the ROW function's array or the SMALL will get the wrong values.
    Let us know if you have any questions.

  14. #14
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Okay, you are making this too much fun! I Can't tell you how much fulfillment I am getting out of this enlightenment. It's motivating me to abandon my sloppy tricks and get efficient like you've shown me!

    I've added a layer of complexity here. I expanded the formula you've helped me understand to a new column called, "Price". The tricky part is, there are three tiers of pricing for these line items, and I'm looking to add a command to the formula that says, "Look in these three columns and return the value in the one that has a value and ignore the others." I've attached an example.

    I'm excited to see how this works... (Or even if it's possible. I seem to be bursting the seams of a flat file and more likely need a database?)

    Also, this has been a very valuable thread for me, as far as my excel education. If there's some link somewhere where I could buy you a pint as a token of my appreciation, I would be happy to!

    Thanks again, in advance!

  15. #15
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2010
    Posts
    503

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    to determine the price using a formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Ohhhh my. My head just about exploded. A COMPLETELY different approach!? WOW! I am attempting to map this formula now... It works well but appears to be a complete departure from the formula discussed earlier on in the thread... How does this one differ? Is one more efficient than the other? The knowledge and command of Excel you guys have is simply astounding. I won't let them call me the "Excel Guru" at work anymore. :/

  17. #17
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    As I'm dissecting the formula, I am encountering all sorts of stuff that I can't decipher.

    =IFERROR(INDEX('Tab 1'!A$2:A$20,AGGREGATE(15,6,ROW($1:$20)/(Table1[Type]='Tab 2'!$B$1),ROWS($5:5))),"")
    I can see that this part defines the source of the info for the formula... Getting info from Tab 1 Rows A2-A20. I'm followng so far.

    =IFERROR(INDEX('Tab 1'!A$2:A$20,AGGREGATE(15,6,ROW($1:$20)/(Table1[Type]='Tab 2'!$B$1),ROWS($5:5))),"")
    I can't grasp this part. What do "15,6" refer to?

    I'm assuming there is not a need to activate an "Array" by using the [CTRL]+[SHIFT]+[ENTER] button combo...

    Ugh I feel like I was so close with the previous Formula... Now I am lost again as I am trying to map this to my project...

    Thank you Czeslaw for the formula!

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Preface: Fully endorse Czeslaw's AGGREGATE function based formulas.
    For the benefit of those who may be using versions prior to the introduction of the AGGREGATE function (2010), here is a SUMPRODUCT based (regular) formula that will work for getting the price for the set up used in the file attached to post #14, paste in D5 and double click to copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Evaluate Formula will show that the price tiers are added then multiplied by the TRUE/FALSE array produced by the (Table1[Item]=B5) argument.
    Let us know if you have any questions.

  19. #19
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    JeteMC...

    It's so exciting to inch closer and closer to a solution! Please know that before I reply and ask for more help, I try with all my might to figure it out on my own. I've attached the latest version (in a new format) and again I am stumped. What am I missing? My formula looks just like yours!!

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    If I understand correctly I think that you want to match the description in the invoice with one of the descriptions on the buildout sheet, if that is correct the formula for Invoice!D8 could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If that's not correct then I suggest that you put the values you expect to see in D8:D12 instead of the ones that the formula yields, and explain where those values come from.
    Let us know if you have any questions.

  21. #21
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    JeteMC...

    Another step closer... Thank you very much for the last direction!

    The formula works perfectly as prescribed, now here's a "but"...

    This formula ADDS Table1[Bill 1 Amount]+Table1[Bill 2 Amount]+Table1[Bill 3 Amount] - is there a way to avoid ADDING these columns? So that the formula returns only a value in the column specified?

    For instance...

    If user selects "1" in D5 on the Invoice1 Tab, then formula returns only the dollar value in Table1[Bill 1 Amount]. (Even if there are values in [Bill 2 Amount] or [Bill 3 Amount]) - instead of the current state where those columns get added?

    Is it as simple as replacing the "+" operator with a comma?

    Updated version attached if needed...

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Paste the following formula into D8 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think that the workings of the formula will be understandable if you run the evaluate formula.
    Edit: Just noticed that there may be as many as 12 'Bill amounts' to choose from so the following formula may be a better option:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Last edited by JeteMc; 12-05-2016 at 01:41 PM. Reason: Added second formula

  23. #23
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    JeteMC,

    I'm digging back into this project, and realizing I guess I've set things up wrong from the start. If you look at the file attached to Post 21, the selection criteria for the formula is Table[Inv]. That was my best attempt at setting up an "indicator" for the formula.

    =IFERROR(INDEX(Table1[Comment],SMALL(IFERROR(ROW($A$1:$A$100)/(Table1[Inv]=$D$5),FALSE),ROW(A1))),"")

    The problem is, that Inv column simply populates based on dollar amounts in the Bill1, Bill2 column, etc. Here's where I've created my issue...

    Each line item will ALWAYS have an amount in Bill1, and commonly have additional amounts in Bill2, Bill3, etc.

    When building the Invoice on the Invoice1 tab, I am hoping to choose a value in D5, then have the formula return the dollar amount in that [Billx] column.

    So if I choose "3" in D5 of the Invoice1 tab, the line item populates where the variable is the dollar amount. It should show the value in the Bill3 column, ignoring the amounts in Bill1 and Bill2. Same result for if I were to choose "1" in D5 of the Invoice1 tab. Return the dollar value in Bill1 and ignore the values in Bill2 and Bill3, etc.

    I probably over-complicated this in the beginning, resulting in the need to now go back and reconsider the formula you've been so helpful with.

    If you think this will be a rigorous endeavor worth of opening a new thread topic, please let me know. Seems like there's probably a simple formula here to do what I need it to do, but I'm a semi-advanced Excel user who can't seem to break the limitations like you gurus can.

    Again, I thank you in advance for your time and energy here.

    Pete

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    See if this does what you want. The Invoice1 sheet is now running off one main array entered formula in the Description that finds whether or not there is a corresponding value in the Billx column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The Date field is populated based on Index/match of the Description column.
    The Amount field uses the second formula from post #22, and column F populates based on whether or not there is a Description on that line.
    Let us know if you have any questions (or if I misinterpreted the request).

  25. #25
    Registered User
    Join Date
    12-01-2016
    Location
    Vacaville, CA
    MS-Off Ver
    2016
    Posts
    17

    Re: Perplexed by IFERROR, INDEX, SMALL, ROW, MIN OH MY! My brain hurts.

    Your wizardry, once again, has benefited me greatly. Many, many thanks. Extraordinary! (Not only did you provide correct solutions for me, you took the time to help teach me, and that's a rare trait. Thank you so much.

+ 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. [SOLVED] IFERROR(MATCH & IFERROR(SMALL Help Needed
    By chad328 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-17-2016, 06:34 AM
  2. My brain hurts - Help creating multi-step array/lookup
    By wallock in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-24-2014, 04:15 AM
  3. my brain hurts!!!!
    By scooby_514 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-07-2014, 04:07 PM
  4. Excel Formula: IFERROR, INDEX, SMALL, MATCH
    By Jenn12788 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2014, 10:03 PM
  5. Comparison for best results to complex for my small brain
    By gordon.worswick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-04-2013, 05:59 AM
  6. My brain hurts. need help figuring this out.
    By Bajungadustin in forum Excel General
    Replies: 2
    Last Post: 06-12-2011, 08:26 PM
  7. [SOLVED] My brain hurts...
    By Buttaflye in forum Excel General
    Replies: 2
    Last Post: 05-10-2005, 02:06 PM

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