Forum Discussion

leolapa's avatar
leolapa
Brass Contributor
Apr 19, 2023
Solved

INDEX multiple matches and criteria, growing lists

Below is a snapshot of a table "People" containing names, eye colors and ages.

 

Then to the right of it, under a light blue background, there is a list with a couple of names and eye colors combinations which I need to retrieve a list of ages from (the matches are bold highlighted on the table).

 

 

E6 (red) successfully returned a spilled result listing all 3 corresponding ages I'm supposed to get from the above combinations.

 

However the formula is static, meaning in case I add more combinations to the above list I need to manually bring an additional set of People[Name] and People[Eye] boolean validation in the FILTER section of the created formula (blue).

 

Say I have a list of 100 names/eye colors combination the formula would be impossibly long.

 

I'm pretty sure there is a way, perhaps by the use of LAMBDA and its enablers (BYROW/BYCOL?), to make this formula pick up additional names/eye colors combinations as the list grows.

 

I'm currently down the BYROW/BYCOL path but as of now with no luck.

 

Any help is greatly appreciated...

 

P.S.: I also attached the file from the shared screenshot.

  • leolapa 

    Seems to do what you want with the sample you shared:

    =FILTER(People[Age],ISNUMBER(XMATCH(People[Name]&People[Eye],E2:E3&F2:F3)))
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    leolapa 

    Seems to do what you want with the sample you shared:

    =FILTER(People[Age],ISNUMBER(XMATCH(People[Name]&People[Eye],E2:E3&F2:F3)))
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Glad you have an option & Thanks for providing feedback

Resources