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!

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::

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

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!

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.

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?

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!

to determine the price using a formula.

Formula:

Please Login or Register  to view this content.

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. :/

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!

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!!

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...

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

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.