How to enter date associated with last volume listed and by prodcut class name

[billrogers184]

I would like to build on my previous thread called "How to enter date associated with last number greater than zero or last zero in row ", which was solved with the formulas is column B7:B21 & C7:C21. I would now like to pull the latest date using the Product Class. I've tried this with an Index Match function in C26:C30 but can only get #N/A as the result. It should look like C35:C39.

Thank you for any support and help.

[AliGW]

Here you go:

=MAX(IF($A$7:$A$21=D26,$C$7:$C$21))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[billrogers184]

Thanks, that did work. Is it possible to adapt the formula to look through the table of volumes?

[AliGW]

Of course!

=MAX(IF($A$7:$A$21=D26,$B$7:$B$21))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

PS If you mean the big table on the right, then that's a complete paradigm shift away from the original question, and it begs another: why not just use your helper columns? You can always hide them. There is NOTHING advantageous to performance in shoe-horning multiple formulae into one, in fact it's harder to maintain (counter-intuitively) and it might even use more processing power. I'd advise against trying to be clever just for the sake of it, and deal with the aesthetics by hiding helpers. Been there, done that, got the T-shirt!!!