Row number variable within a Horizontal SMALL

[greggwallace]

Hi All

I've been working through a complicated costing workbook. I've nearly completed everything task apart from one lookup table. This isn't the final workbook but I've stripped the problem out to make it a little clearer. I have one sheet which is the data source "Cost Data"

And another which I'm trying to create a report that lists part number, date of cost and the cost.

I've stripped out the problem onto one tab below to simplify the problem.

So the problem....... I have the following formula in D16

=INDEX(Table13[[#Headers],[Jan-10]:[Nov-10]],,SMALL(IF(B6:J6>0,(COLUMN(B6:J6)-1)),C16))

I will eventually build up a complete formula but for now I've broken out the variable into B16 & C16. C16 works fine and i will use the "Helper Column" on the report sheet to give me the next date.

the last part of the problem is changing the ROW that the SMALL is using to look up the values. i think i have two options. Either use the ROW number to define the search row somehow or look up the part number to define the row.

This is pretty much where I'm stuck. i cant seam to come up with a way to change the ROW range dynamically. "B6:J6". I'm guessing it is either using INDEX MATCH some how or a VLOOKUP, but i cant quite figure it out.

Any hints in the right direction would be appreciated.

 

[jasonb75]

As you're using 365 and will have dynamic array functions, a better way to do it would be (note that as you have posted screen captures instead of usable XL2BB samples, this formula is untested and may contain typos).

=TRANSPOSE(FILTER(Table13[[#Headers],[Jan-10]:[Nov-10]],INDEX(Table13[[Jan-10]:[Nov-10]],MATCH(B16,Table13[[Par Numbers]:[Par Numbers]],0),0)<>""))

Note that the formula is based on the simplified single tab where there is a missing t in the 'Part Number' heading.

 

[greggwallace]

As you're using 365 and will have dynamic array functions, a better way to do it would be (note that as you have posted screen captures instead of usable XL2BB samples, this formula is untested and may contain typos).

=TRANSPOSE(FILTER(Table13[[#Headers],[Jan-10]:[Nov-10]],INDEX(Table13[[Jan-10]:[Nov-10]],MATCH(B16,Table13[[Par Numbers]:[Par Numbers]],0),0)<>""))

Note that the formula is based on the simplified single tab where there is a missing t in the 'Part Number' heading.

Thanks. I did come across filter but haven't had the chance to explore it properly. I'm sure this is what i will end up using in the future but in the meantime (after a few days of playing), I've managed to use the AGGREGATE(15,6, Function, Combined with a dynamic range formula (INDEX(MATCH)) to get the results I need. A few headaches with cells containing formula's that return a black cell ( i think caused by excel not truly seeing the cell as blank or perhaps as Text etc) but I've managed to find some work arounds. Thanks for your comments, I was struggling with this one.