Find the table entry with the largest number, only if it matches a condition

user_5058405 0
Viewed 36

I have a table like below and I want to return the name of the item with the greatest effect of a particular type. For example, I want the name of the ring with the best 'Shield' enchantment, in this case 'Brusef Amelion's Ring'.

Description Apparel slot Effect Type Effect Value
Apron of Adroitness Chest Fortify Agility 5 pts
Brusef Amelion's Ring Ring Shield 18%
Cuirass of the Herald Chest Fortify Health 15 pts
Fortify Magicka Pants Legs Fortify Magicka 20 pts
Grand ring of Aegis Ring Shield 6%

I've tried using a MAXIFS statement:

=maxifs(Effect Value, Apparel Slot, "=Ring", Effect Type, "=Shield")

and that returns 0.18, as I'd expect. But I want it to return the name of this item, 'Brusef Amelion's Ring'. So I then tried using a vlookup on this value, but there doesn't seem to be the option to only lookup a value if ('Apparel Slot'='Ring' && 'Effect Type'='Shield'), for example.

I feel like there must be a way of nesting some specific functions here, but I can't quite figure it out.

Is there any way to do this while avoiding manually sorting and filtering my data before running each query?

google-apps-script google-sheets
1 Answers

Is this what you are looking for?

=DGET(A1:D6,"Description",{"Apparel slot","Effect Value";"Ring",MAXIFS(D2:D6,B2:B6,"Ring",C2:C6,"Shield")})

DGET function needs column headers to work with as you can see. So we want the value from the column Description. "Originally" this is a DGET function:

DGET(A2:F20,"price",{"Ticker";"Google"})

This is saying: Find price where ticker = google.

We can enhance this a bit by entering two criteria's this is done , or \ separated (depends on your country settings) like this:

DGET(A2:F20,"price",{"Ticker","Year;"Google",2020})
DGET(A2:F20;"price";{"Ticker"\"Year;"Google"\2020})

Find price where ticker = google and year = 2020.

Ofcource you can replace "Google" with a cell reference. Hope this helps.

user_134611830
Related