I am working on a report to show the top 20 producers in a given state that are on target for an award. It worked all well and good last month, but this month I encountered an issue with the names not accurately reflecting the results. I have attached a sample file below. The worksheet "Tracker" contains source data (which is hidden with the file locked) while the "Summary" worksheet is the user facing sheet that shows up to the top 20 producers in each state.
The issue I'm encountering is pulling back the names if there are duplicate point values. In the sample file, there are two producers with 100,000 points; one in DE and one in OH. When I select DE, the name is correct but when I select OH, because the lookup is off of the point value, it's still pulling back the DE producers name. Since they are being ranked based on points, I'm not sure how else to perform the lookup. I don't want to add a helper column on the Tracker worksheet because it is a file that comes from another source and I want any future person in my role to be able to copy, paste, and send without manual adjustments. I'm sure I'm missing something simple, but I can't figure out what.
Thank you for your help.
Test File.xlsx
Bookmarks