Hello All 🙂
I have an index and match formula that works and I need to now vary this slightly and I cannot figure out how to get it to work.
What the formula does is look for a value on sheet 1, match the value on the SeriesSales sheet, then search the next 10 rows for "Site Total" and when it finds that it returns the $ value in column 3. Here is the formula
=IFERROR(INDEX(INDEX(SeriesSales!$C$1:$C$2000,MATCH($A10,SeriesSales!$A$1:$A$2000,0)):INDEX(SeriesSales!$C$1:$C$2000,MATCH($A10,SeriesSales!$A$1:$A$2000,0)+10),MATCH("Site Total",INDEX(SeriesSales!$A$1:$A$2000,MATCH($A10,SeriesSales!$A$1:$A$2000,0)):INDEX(SeriesSales!$A$1:$A$2000,MATCH($A10,SeriesSales!$A$1:$A$2000,0)+10),0)),"$0.00")
My problem is I now need to find the value, then search the following rows from that cell with the value until the first instance of "Site Total" is found, and then return the $ value shown in column 3. This text string could be 4 rows down or as many as 40+ rows down or anywhere in between
I hope I have explained this clearly enough!
Any help will be greatly appreciated 
It would help if you upload a sample file so we can see your question in context.
Hi Mynda
Attached is a small example of the file - the problem I have is that site total can appear any number of rows after the site heading. Hope this helps to make my problem clearer? The original spreadsheet contains 1000+ rows. I need a formula solution as this is part of a larger spreadsheet that summarises data from a variety of files and different sources for monthly reporting.
Thank you 🙂
Thanks for sharing a sample of your data. I don't recommend any formulas for this. The data really should be cleaned, then you can use PivotTables to summarise the data without any risk of formula errors.
In the attached file I've used Power Query to reformat the data into a tabular layout using Power Query. I've then summarised it in a PivotTable (see sheet Query & Pivot).
I've also added another PivotTable that summarises the data by employee and service by location to illustrate the different information you can get from the data when you format it correctly and use PivotTables.
Note: I've saved the file as a .xlsx which is compatible with Excel 2007 onwards. Power Query is available in Excel 2010 onwards. You can download Power Query here.
Please let me know if you have any questions.
Mynda
Thank you Mynda but I cannot see any attachment? Where do I go to find that to download?
Oops, sorry I forgot to press 'Start Upload'. Here it is.
Thank you very much Mynda this works exactly as I needed 🙂 I have looked at how you wrote the query and think I have managed to follow the very creative way that you came up with a resolution! I have another similiar report that needs to have data extracted over many hundreds of rows and whilst this solution won't work with that data it has given me some inspiration to "give it a go". I see that you have a course in power query which I think would be very helpful so I'm going to ask my boss if I can sign up for it.
Once again thank you very much for your assistance.
You're welcome. I look forward to having you join my Power Query course. I'm sure you'll love Power Query and wonder how you lived with out it 🙂
Mynda
