To Lookup For Top Name of Column And Row In A Chart For A Specific Value And Create Another Chart

mohsin91santono-_-1220 

Here you go! :-) I developed a solution only based in formulas and not requiring Power Query.

Data Table:

I transformed the data table into an Excel Table. That will allow you to add more data at the bottom without having to update the ranges in the formulas. The formulas will always know that they need to look at the entire data table, regardless of the number of rows.

I added three extra columns to the table that will be responsible to check if the columns B, C and D have dates that coincide with the date in cell I2. You can hide these three helper columns if you'd like.

Cell I2 was given the name ChosenDate.

I added conditional formatting rules to the data table. This way, any date that matches the ChosenDate will be automatically highlighted. If you want this feature to keep working, you cannot paint the cells of the data table.

Results Table:

I added two helper columns to this table that are responsible to pick all the matching entries in the data table. You can hide these two columns if you'd like.

I added the formulas to the report columns.

The report table can show up to 25 results. You can copy the formulas down if you need more result rows.

The final file is attached.

I hope this helps.

Celia Alves