Table lookup and returning an answer based on a specific condition

[BritsBlitz]

Hi. I'm looking for an Excel formula that will do the following based on the table below:

Column A	Column B
Apples	No
Apples	Yes
Oranges	No
Oranges	No
Oranges	Yes
Peaches	No
Peaches	No

I need a formula that will look at all the lines of Apples and if any of the lines has a "Yes" in Column B, then it needs to return a TRUE result. The same for the lines with Oranges; the formula needs to look at all the lines with Oranges and if any of them has a "Yes" in Column B, then it needs to return a TRUE result. Same for Peaches, etc.

Here's he part I'm struggling with: I cannot used a pre-defined range for Apples, Oranges and Peaches because sometimes there will be 2 lines with Apples and sometimes there will be 10 lines with Apples so the formula first needs to look at all the lines in the table, then identify the lines with "Apples" and then look within each line for a "Yes". It doesn't matter how many "Yes" entries there are within all the lines of Apples, as long as there is at least one, it should return a TRUE result.

I need to use one formula to search the entire table with the entries in Column A being my "triggers". For instance, if I want results based on Apples, the formula should return the result from above for the lines with Apples. If the trigger is Oranges, then the result should be based on only the lines with Oranges, etc.

 

[hagia_sofia]

What version of Excel are you using? The result you want is that for all rows that meet the condition there should be TRUE or are you expecting just one answer, i.e. that there is a row that meets the condition?

 

[BritsBlitz]

What version of Excel are you using? The result you want is that for all rows that meet the condition there should be TRUE or are you expecting just one answer, i.e. that there is a row that meets the condition?

I'm using Excel for Microsoft 365 (Version 2406, build 16.0.17726)

I need one answer per fruit. On another sheet I have a table like the one below. If there is a "Yes" for any of the lines with apples, the table below for Apples will be updated with a "True". If there is a "Yes" for any of the lines with Oranges, the table below for Oranges will be updated with a "True", etc. Based on the table in my question, the result would be as shown as follows:

Appels	True
Oranges	True
Peaches	False

 

[Peter_SSs]

I'm using Excel for Microsoft 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

BritsBlitz.xlsm
	A	B
1	Column A	Column B
2	Apples	No
3	Apples	Yes
4	Oranges	No
5	Oranges	No
6	Oranges	Yes
7	Peaches	No
8	Peaches	No
9
10
Sheet2

BritsBlitz.xlsm
	A	B
1
2	Apples	TRUE
3	Oranges	TRUE
4	Peaches	FALSE
5
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=COUNTIFS(Sheet2!$A$2:$A$1000,A2,Sheet2!B$2:B$1000,"Yes")>0

 

[BritsBlitz]

I'm using Excel for Microsoft 365 (Version 2406, build 16.0.17726)

I need one answer per fruit. On another sheet I have a table like the one below. If there is a "Yes" for any of the lines with apples, the table below for Apples will be updated with a "True". If there is a "Yes" for any of the lines with Oranges, the table below for Oranges will be updated with a "True", etc. Based on the table in my question, the result would be as shown as follows:

Appels	True
Oranges	True
Peaches	False

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

BritsBlitz.xlsm
	A	B
1	Column A	Column B
2	Apples	No
3	Apples	Yes
4	Oranges	No
5	Oranges	No
6	Oranges	Yes
7	Peaches	No
8	Peaches	No
9
10
Sheet2

BritsBlitz.xlsm
	A	B
1
2	Apples	TRUE
3	Oranges	TRUE
4	Peaches	FALSE
5
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=COUNTIFS(Sheet2!$A$2:$A$1000,A2,Sheet2!B$2:B$1000,"Yes")>0

Thank you. This works perfectly. Since I don't know how many entries the table might have at any particular time, I changed it to : =COUNTIFS(A:A,A2,B:B,"Yes")>0

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.