Formula for identify related cells based on a common criteria

[ishpahuja]

Hello everyone
I am looking to identify family members who are eligible for a benefit. Let's say govt. has identified a set of eligible fruits that qualify under benefits. If any family member sells one of those fruits, all family members become eligible for benefits. All family members are coded with a family ID. I am looking to find a formula to identify if someone is eligible or not.
For e.g.
For family ID 1111 - the first member sells Apple while second doesn't do anything - since apple is part of eligible fruits, both family members are eligible and coded under "Apple" category.
For family ID - 3333, first member sells papaya (not eligible fruit), but second member sells orange (eligible fruit), so orange should appear against these two family IDs.
For Family ID 2222 - only one member under eligible list, so "mango" appears.
For Family ID 4444 and 9999 - they and their family members don't sell eligible fruits, so nothing should appear against their names.

See in image the details and output column.

I tried for formula, but it appears it is more complicated than I think.
Can you help?

 

[jasonb75]

Array confirm in J2 with Ctrl Shift Enter, then fill down.

=IFERROR(INDEX($L$2:$L$5,MATCH(TRUE,COUNTIFS($H$2:$H$10,H2,$I$2:$I$10,$L$2:$L$5)>0,0)),"")

Note that if you do not confirm the array correctly then all results will be blank.

 

[ishpahuja]

Array confirm in J2 with Ctrl Shift Enter, then fill down.

=IFERROR(INDEX($L$2:$L$5,MATCH(TRUE,COUNTIFS($H$2:$H$10,H2,$I$2:$I$10,$L$2:$L$5)>0,0)),"")

Note that if you do not confirm the array correctly then all results will be blank.

It didn't work

See image

 

[jasonb75]

Looks like you didn't follow the instructions correctly.

Array confirm in J2 with Ctrl Shift Enter, then fill down.

You have filled down before confirming the array.

 

[ishpahuja]

Can you please let me know wh

Looks like you didn't follow the instructions correctly.
You have filled down before confirming the array.

Thanks a lot. I corrected it. You are a genius! Thanks again.

 

[ishpahuja]

Would you mind sharing your logic? All this time, I was thinking it is quite difficult, and you made it sound like so simple.

 

[jasonb75]

The logic is in countifs, this looks to see how many family members there are for each fruit type in the list. The rest of the formula looks to see which is the first one with a count more than zero.

COUNTIFS($H$2:$H$10,H2,$I$2:$I$10,$L$2:$L$5)

 

[ishpahuja]

The logic is in countifs, this looks to see how many family members there are for each fruit type in the list. The rest of the formula looks to see which is the first one with a count more than zero.

COUNTIFS($H$2:$H$10,H2,$I$2:$I$10,$L$2:$L$5)

Thank you so much. It helped a lot. Thanks once again.