SOLVED

INDEX multiple matches and criteria, growing lists

Brass Contributor

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 (Brass Contributor)
Solution

@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)))
Easy peasy... Thank you sir!
Glad you have an option & Thanks for providing feedback
1 best response

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

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

View solution in original post