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):

WA102249 Q example

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?

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.