Table lookup and returning an answer based on a specific condition

BritsBlitz

New Member
Joined
Jan 10, 2014
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi. I'm looking for an Excel formula that will do the following based on the table below:

Column AColumn B
ApplesNo
ApplesYes
OrangesNo
OrangesNo
OrangesYes
PeachesNo
PeachesNo

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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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?
 
Upvote 0
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:

AppelsTrue
OrangesTrue
PeachesFalse
 
Upvote 0
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
AB
1Column AColumn B
2ApplesNo
3ApplesYes
4OrangesNo
5OrangesNo
6OrangesYes
7PeachesNo
8PeachesNo
9
10
Sheet2


BritsBlitz.xlsm
AB
1
2ApplesTRUE
3OrangesTRUE
4PeachesFALSE
5
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=COUNTIFS(Sheet2!$A$2:$A$1000,A2,Sheet2!B$2:B$1000,"Yes")>0
 
Upvote 0
Solution
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:

AppelsTrue
OrangesTrue
PeachesFalse

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
AB
1Column AColumn B
2ApplesNo
3ApplesYes
4OrangesNo
5OrangesNo
6OrangesYes
7PeachesNo
8PeachesNo
9
10
Sheet2


BritsBlitz.xlsm
AB
1
2ApplesTRUE
3OrangesTRUE
4PeachesFALSE
5
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=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
 
Upvote 0

Forum statistics

Threads
1,223,702
Messages
6,173,959
Members
452,539
Latest member
delvey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top