How can I correct the #calc error for this formula?

Hi @Patricia Stephenson,

Thank you for posting your question in the Microsoft Q&A forum.
Based on your sharing, there're some conditions may have caused the #CALC! error:

• The f is empty if no rows match the value in F2, which lead to the subsequent functions fail.
• If no values match the latest date, the FILTER(TAKE(f,,-1).....SORT(c,,-1),1))) returns nothing and also lead to the LOOKUP function fails.

Therefore, we suggest you should double-check the formula in helper cells step-by-step:

• Check if f is empty by using =FILTER(A2:C39954, A2:A39954=F2)
• Check if c has values by using =CHOOSECOLS(FILTER(A2:C39954, A2:A39954=F2),2)
• Check the latest date by using =TAKE(SORT(CHOOSECOLS(FILTER(A2:C39954, A2:A39954=F2),2),,-1),1)

Note: Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.  

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.  

Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.  

I'm looking forward for your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Hi,

If column A contains unique values, then the simplest and most efficient formula would be:

=XLOOKUP(F2, A2:A39371, C2:C39371)

It directly returns the value from column C that matches the value in F2 from column A — no need for sorting or filtering.

However, if column A contains duplicates and you want the value from column C that corresponds to the maximum value in column B for a given F2, then this formula is a simpler and more robust alternative to your original one:

=TAKE(TAKE(SORT(FILTER(A2:C39371, A2:A39371 = F2), 2, -1),, -1), 1)

It filters rows by F2, sorts them by column B descending, and returns the corresponding value from column C for the highest B.

Let me know if these formulas worked for you or if you need any further help!

Good day @Patricia Stephenson, 

Hope things are running smoothly on your end.

Is there any update on this issue? Have you successfully resolved the situation yet? If you are still facing the same issue or need assistance with anything else, kindly respond to this email, and I will be happy to help.

We want to make sure everything is working as expected and that your experience remains uninterrupted.

Thank you for your patience and understanding. Hope to hear from you soon.

Hi @Patricia Stephenson, 

I hope you are having a great day.

I'm reaching out again to follow up on your issue and check if everything has been resolved. My goal is to ensure your experience remains smooth and hassle-free. If you're still encountering any problems or have run into new challenges, please let me know which steps you're currently stuck on and happy to provide further help whenever you need it. 

If you have any further questions, feel free to tag me in your reply so I can assist you directly.

Looking forward to your update.