Aggregating data of multiple / same vlookup references

Like so, some formulas can reference the whole column, others need a more defined range to work efficiently:

Reload this thread and try again.

Columns B & C

INDEX/MATCH is a standard lookup formula. The MATCH() formula finds the first instance of the searched value and returns a numeric position back to the indexed range.


Column D, F & I

A standard SUMIF() that is summing all the values in a specific column based on spotting the rows that match the symbol in column A.


Column E & H

Another standard SUMIF() adding all the values in the specified column after matching the column A symbol, then dividing that sum by the number of entries overall using a COUNTIF(). It's a "manual average" if you will.


Column G

One of my favorite "trick" formulas. Your last "sell date" isn't always on the last row with a particular symbol, many of the "last" entries are blank in the sell column. This formula is making an array list of all the rows that match the column A symbol and are greater than zero in the sell date column. Then the formula decides which one is the entry furthest down on the sheet and returns that date from column H.




If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

You can turn on the AUTOFILTER for that data set and filter the data inplace by any value(s) you want. Once filtered, you would be looking at all the ABT rows, for example, all the others hidden. You can edit/print as needed, then filter a different way or turn it off.

No need for separate sheets, but if you really want one, once filtered, you can copy all visible rows to another sheet.

yes, a manual autofilter/copytoanothersheet can just as easily be a vba-based autofilter/copytoanothersheet/repeatformultiplecriteria kind of thing.

Sure that would work. Set up your sheet where you list securities, and then put some securities in there and then manually mockup what a "report" would look like based on that.

It's remarkably simple to record a macro to Autofilter a sheet based on specific criteria and paste the results elsewhere. Adding a "loop" to do multiple filters is not much more work. The real question is always what your data/criteria/final report(s) look like.