I want to search for text within a column & then search a range that depends on that text

[hazem_omari]

Hi All,

First of all I want to admit that this might be a problem that have been posted before, but I really tried my best to find a solution with google and within this forum with no luck, so here is the problem, and I hope you would understand:

I have a data sheet that I receive monthly with rows and order that change each month, and I am now doing a summary sheet that extract certain numbers of interest from the data sheet, and I am now in the process of automating that summary sheet, below is example of the data I have:

I have this in my data sheet,

Awareness

Brand(A) 10 20 30
Brand(B) 55 43 23
Brand(C) 60 88 22


Trial

Brand(A) 50 60 70
Brand(B) 80 85 60
Brand(C) 80 95 30


Usage

Brand(A) 60 70 80
Brand(B) 70 60 50
Brand(C) 70 99 40


(FYI the columns represent months),So I have the same list of brands that is repeated for about 10 or more tables , and what I need to do is to search for the text"Awareness" and then search the relative range below for another text"Brand(A)" it just before the start of the next table, to return whatever column i need. and then I shall repeat this for the other tables I need, (i.e. Trial, Usage, etc)


I will tell you what I reached by myself so far, I succeeded to reach the same end result using both offset and match formulas, but the drawback of this approach is that new brands (Rows) might be inserted each month, which make my offset really useless, so that is why I thought posting the above, to reach a better and more consistent approach that is changeproof.

Thanks in Advance,
Hazem

[JohnTopley]

Please post a sample file showing expected results.To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[hazem_omari]

Hi John, the output will look exactly the same, the thing is I need formulas to get these values based on text from my monthly data to update my summary sheet instead of copy paste everything every month.