SOLVED

# Issue with retrieving names based on criteria + max, tried FILTER, INDEX, VLOOKUP

Brass Contributor

# Issue with retrieving names based on criteria + max, tried FILTER, INDEX, VLOOKUP

Hello all - I'm trying to create a formula that I can use in the right table that references information in the left table and returns the following:

- Name of Character with the highest profession value relative to that profession

I've tried INDEX, FILTER, VLOOKUP and CHOOSECOLS and nothing will give me the name of the character for the profession. INDEX was alright but it returned the first character with the highest profession (regardless of being filtered to just a single profession or not) - so it would return Appwarrior for Enchanting instead of Appdk.

Any insights, thoughts?

2 Replies

# Re: Issue with retrieving names based on criteria + max, tried FILTER, INDEX, VLOOKUP

=INDEX(CharacterProfs[Character], MATCH(MAXIFS(CharacterProfs[Vanilla], CharacterProfs[Profession], "Enchanting"), CharacterProfs[Vanilla], 0))

here was the index formula I was using

=VLOOKUP(MAXIFS(CP[Vanilla], CP[Profession], "Enchanting"), CP[[Character]:[Vanilla]], 2, FALSE)

here was the vlookup formula i was using
best response confirmed by keltzjd (Brass Contributor)
Solution

# Re: Issue with retrieving names based on criteria + max, tried FILTER, INDEX, VLOOKUP

See the suggestions with VLOOKUP, INDEX and MATCH and FILTER in the attached file.

=VLOOKUP(1,CHOOSE({1.2},(MAXIFS(CharacterProfs[Vanilla], CharacterProfs[Profession], F10)= CharacterProfs[Vanilla])*(CharacterProfs[Profession]=F10),CharacterProfs[Character]),2,FALSE)

=INDEX(CharacterProfs[Character], MATCH(1,(MAXIFS(CharacterProfs[Vanilla], CharacterProfs[Profession], F10)= CharacterProfs[Vanilla])*(CharacterProfs[Profession]=F10), 0))

=FILTER(CharacterProfs[Character],(MAXIFS(CharacterProfs[Vanilla], CharacterProfs[Profession], F10)= CharacterProfs[Vanilla])*(CharacterProfs[Profession]=F10))

1 best response

Accepted Solutions
best response confirmed by keltzjd (Brass Contributor)
Solution

# Re: Issue with retrieving names based on criteria + max, tried FILTER, INDEX, VLOOKUP

See the suggestions with VLOOKUP, INDEX and MATCH and FILTER in the attached file.

=VLOOKUP(1,CHOOSE({1.2},(MAXIFS(CharacterProfs[Vanilla], CharacterProfs[Profession], F10)= CharacterProfs[Vanilla])*(CharacterProfs[Profession]=F10),CharacterProfs[Character]),2,FALSE)

=INDEX(CharacterProfs[Character], MATCH(1,(MAXIFS(CharacterProfs[Vanilla], CharacterProfs[Profession], F10)= CharacterProfs[Vanilla])*(CharacterProfs[Profession]=F10), 0))

=FILTER(CharacterProfs[Character],(MAXIFS(CharacterProfs[Vanilla], CharacterProfs[Profession], F10)= CharacterProfs[Vanilla])*(CharacterProfs[Profession]=F10))