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

Occasional Contributor

Hello There,


I need big help.

Attached images is self explanatory.


Let me know if you need any other information


Let's say, I need to create alert system for a specific date



10 Replies

Hi, @mohsin91santono-_-1220

Power Query would be one way to solve this. It comes with Excel in the most recent versions. Which  version of Excel do you use? Can you please attach the file? I will help you with this.

I am using office 2013
Thanks for the response :)
Could you please help?


Please see here how to install Power Query in your Excel:


This is a powerful tool that you don't want to miss in Excel.

Let me know when you have it and please send a sample of the file. I will create an automation with PQ for you on your file. All you will have to do after that is to add new data and click a button to update your results.


Please find the sample file.
Please follow original post to provide formula. (you shall find my original query explained in an image)

Many thanks.



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 



Hi Celia,


Thanks A Lot :D
It works :D
You saved my **bleep** :D


Here is the answer to your message asking how to adapt the formulas for when you want to get the results for dates in between two given date values.

Let me know if you have any questions. 



Thanks again. You are Awesome, dear.
Even I also tried yesterday in separate way , it worked (Thanks to insights already shared by you before)

But you did it more smartly.

Advance Excel is beautiful.

Yes, Excel is the best!

The great thing is that there is no unique solution. I am glad that you were able to find an answer to the challenge on your own. Well done! ;-)

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies