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

 

 

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

2 Replies

  • 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