Summarize the best options in another table

Question

I need help to create a sheet for a game. I am providing the INPUT, and I would like to create the OUTPUT in another tab (do not consider the colors, its just for ease of understanding):

Mainly, the INPUT will be many locations with different items and each item with a % chance of being dropped in that location.

The OUTPUT need to be a summary of each item (only once and ordered by Item name, if possible) showing the location with the higher drop chance.

Is this possible?
If no, how could I change my INPUT to achieve an OUTPUT like this?

Answer 1

The following works, but requires you to have a hidden column. First, a query, let's say in cell D3:

=query(A3:C, "select max(C), B group by B order by B desc label max(C) ''", 0) 

This selects the maximal % for each name, and orders names alphabetically listing only once. The "label" part avoids an unnecessary column label. The output will have two columns: D and E. Column E is what it should be in your output. Column D is the "chance" that you want to have in G, but since it's in a wrong place, it may be hidden.

Then in cell F3 use

=arrayformula(vlookup(filter(D3:D, len(D3:D)), {C3:C, A3:A}, {2, 1}, False))

which will look up every nonempty value in column D (that is, a max %) in column C of the sheet, and return the corresponding value from A, as well as from C itself. That is, it gives the location and chance.