Forum Discussion

keltzjd's avatar
keltzjd
Brass Contributor
Jul 05, 2024
Solved

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 pr...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jul 06, 2024

    keltzjd 

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

keltzjd's avatar
keltzjd
Brass Contributor
Jul 05, 2024
=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
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 06, 2024

    keltzjd 

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

Resources