Array Formula

• Hi All,

Continuing my learning Phase, I have attached a workbook which shows companies by their contractual status. i.e. Main Contractor, Supplier etc.

Tab "Store Details" lists sample company registration data
Tab "All Data" lists the audit scores for all companies

Tab "Data by Month" is where I would like to be able to filter data based on the selections made on the worksheet.

I need some help to create an array formula that will populate the company names
and to then lookup the data and populate the scores from the "All Data" tab

I hope the work sheet explains this clearly.

Thank you

Files

• Your learning phase will never end, believe me!

Is there any flexibility in the layout? You are making things very complicated for yourself by doing it in the way you have mocked it up.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Hi Ali,
Unfortunately there is no flexibility in the structure. I am working around an old workbook which has vast amounts of data.
I am adding the worksheet "Data by Month" into the old work book.

Thanks for any help

• If it were me, I'd be updating things. It's never too late to modify!

Hopefully someone will have the time today to help you with this, but I have run out, I'm afraid. It's not a quick job.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Hi Ali,

No problem appreciate the though anyhoo.
Attached is an updated version of the workbook where I have added an array formula to competed the first part of my plight.

I now need an index match formula to lookup the rest.

Thank you

Files

• Merged cells is always a pain in the rear.....

Start off with a "helper row" starting in D5 of Data By Month sheet, enter formula:

=IF(D6="",C5,D6)

copied across to end of last table. You can then hide row if desired.

Then in D8 use formula:

=IF(\$C8="","",INDEX('All Data'!\$C\$4:\$CA\$13,MATCH(\$C8,'All Data'!\$B\$4:\$B\$13,0),MATCH(D\$5,'All Data'!\$C\$2:\$CA\$2,0)+COUNTIF(\$D\$5:D\$5,D\$5)-1))

copied across the first table and down to bottom including where there are blank dates.

Then copy all those cells directly to next table, then next table and so on.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Once Again,
Appreciate the help and for the nice solution.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!