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


INDEX Multi Matches, Criteria.png


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.

3 Replies
best response confirmed by leolapa (Contributor)


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

Easy peasy... Thank you sir!
Glad you have an option & Thanks for providing feedback