• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reverse Lookup / Duplicate Values

MSC Bobs

Member
Hello Everyone,

I'm hoping somebody can help me with a problem.

I'm trying to do a reverse look up, but there are duplicate values and so the reverse look up is not displaying unique values, as it should.

I feel like I'm very close, but I haven't been able to reach a solution and so I'm turning here for help.

Thank you.
 

Attachments

  • Reverse Lookup.xlsx
    28.4 KB · Views: 7
Hi:

Do a pivot do what you are looking for.

Thanks
 

Attachments

  • Reverse Lookup.xlsx
    29.9 KB · Views: 5
That was easy. Thanks for the tip.

I always forget about using pivots because I try to write formulas first. I've always found pivots clunky and awkward, even though I know they are powerful.

With this spreadsheet, I'm just working out formulas right now because it will eventually be a dashboard.

I plan on inserting different game dates into a drop down menu. Is it possible for a pivot table filter to link up with a drop down menu? I'm just trying to figure out how to work in the interactivity.
 
I'd recommend Pivot as well. You'd either control Pivot through slicer or VBA.

Only time I'd advise against using Pivot is when you have a lot of specialized calculations (such as moving daily Service Level %, along with SL for each 15 min interval etc etc).

As for the formula you can use something like below.
In B11:
=INDEX(tbl.Roster,MATCH(0,COUNTIF($B$10:$B10,tbl.Roster[Player])+(tbl.Roster[Market]<>Dashboard!$A$2)+(tbl.Roster[Date]<>Dashboard!$A$3)+(tbl.Roster[Minutes]=0),0),7)

Entered as array (CSE) and copied down.
 
Another One!

CSE

=IFERROR(INDEX(tbl.Roster[Player],SMALL(IF((tbl.Roster[Market]=$A$2)*(tbl.Roster[Date]=$A$3)*(tbl.Roster[Minutes]>0),ROW(tbl.Roster[Player])),A11)-1),"")
 
These are all very good solutions and you've all given me a lot of great options. Thank you for devoting the time to assist me. I really appreciate it!
 
Back
Top