I have an excel worksheet which includes 11 columns and over 4,000 rows of data. One column (column G) contains a large amount of data which includes text and numbers (e.g. item#1 8253 TFD, item#2 5895 JuuN, item# 5534 TFJ...) The adjacent columns contain date, customer name, amount of invoice etc.

What I am looking for is the ability to find all cells in column G which contains certain data (e.g. 8353) and return the customer name, amount and date and also be able to sum the values returned in the amount column.

So, I created a pivot table on a separate sheet for this data. The problem in using a pivot table is that, while you can select multiple items from a drop down list, that requires going through the entire list, identifying where the text occurs you are looking for and checking the box next to it. We have literally hundreds of text items we are looking for (not just 8253 for example). This gets really tedious and is prone to error.

So, I was wondering whether I could use a search criteria formula (maybe VLOOKUP or SEARCH?) which would identify all cells in column G which contain the specific text I am looking for and return the corresponding data to another part of thw worksheet and would allow you to sum the results in a sperate table.

Anyone have ideas on how to do this?

Thanks