formula to find value in cell, then find value in adjacent cell and return yes if adjacent cell contains specific text

B

birbs

New Member
Joined
Nov 11, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet with a column of names, email addresses and organizations. I want Excel to find each name in a column on a separate sheet, find its organization, and return yes if organization contains specific text.

For example:

Sheet 1

NameemailOrganization
John Doejohndoe@email.comACME Incorporated
Jane Pateljanepatel@email.comGoogle
Melissa Williamsmelissawilliams@email.comFacebook
Eric Smithericsmith@email.comAmazon
Erica Williamsonericawilliamson@email.comACME Company

Sheet 2

NameemailACME?
John Doejohndoe@email.comYes
Jane Pateljanepatel@email.comNo
Melissa Williamsmelissawilliams@email.comNo
Eric Smithericsmith@email.comNo
Erica Williamsonericawilliamson@email.comYes


Thanks in advance!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Welcome to the MrExcel board!

If Eric's formula is tweaked a little, then it only needs to be entered in the top cell and all the results will automatically 'spill' down to the other rows.

birbs.xlsm
ABC
1NameemailACME?
2John Doejohndoe@email.comYes
3Jane Pateljanepatel@email.comNo
4Melissa Williamsmelissawilliams@email.comNo
5Eric Smithericsmith@email.comNo
6Erica Williamsonericawilliamson@email.comYes
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IF(COUNTIFS(Sheet1!$A$2:$A$10,A2:A6,Sheet1!$C$2:$C$10,"*ACME*"),"Yes","No")
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

If Eric's formula is tweaked a little, then it only needs to be entered in the top cell and all the results will automatically 'spill' down to the other rows.

birbs.xlsm
ABC
1NameemailACME?
2John Doejohndoe@email.comYes
3Jane Pateljanepatel@email.comNo
4Melissa Williamsmelissawilliams@email.comNo
5Eric Smithericsmith@email.comNo
6Erica Williamsonericawilliamson@email.comYes
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IF(COUNTIFS(Sheet1!$A$2:$A$10,A2:A6,Sheet1!$C$2:$C$10,"*ACME*"),"Yes","No")
Dynamic array formulas.
Click to expand...
Eric W said:
Welcome to the MrExcel forum!

One way:

Book1
ABC
1NameemailACME?
2John Doejohndoe@email.comYes
3Jane Pateljanepatel@email.comNo
4Melissa Williamsmelissawilliams@email.comNo
5Eric Smithericsmith@email.comNo
6Erica Williamsonericawilliamson@email.comYes
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IF(COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$C$2:$C$10,"*ACME*"),"Yes","No")
Click to expand...

Hi, thanks so much for these, but these did not work for me.

I realized that the spreadsheets I shared as an example do not accurately reflect what I need help with, I apologize.

Let me try this again.

Sheet 1 (de-identified names and email addresses)

Full name2 (sorted A-Z)EmailOrganization
Abe Andrewsabeandrews@email.comOther – Please describe here:
Barbara Bathbarbarabath@email.com
Crispin ChuckyCrispinchucky@email.comNo/Don't know/Not sure
Ellison Smithdavedrew@email.comNIDILRR project (i.e. you work on a NIDILRR award)
Elise Williamseliseelsin@email.comNo/Don't know/Not sure
Francis FudgeNo/Don't know/Not sure
Gerald GerdNo/Don't know/Not sure`

Sheet 2:

Name (sorted A-Z)emailNIDILRR?
Allison Appleallisonapple@email.comNo
Beth Beddenbethbedden@email.comNo
Crispin Chuckycrispinchucky@email.comNo
Dave Drewdavedrew@email.comNo
Ellison Smithellisonemith@email.comYes


Sheet 1 contains registrants of a conference. Sheet 2 contains attendees of a conference. As you can see, there's more registrants than attendees, and the registration list has some blank entries. Furthermore, the attendee list does not directly match the registration list line by line, the names are mixed up. Although "Crispin Chucky" and "Ellison Smith" are contained on both lists, only "Ellison Smith" should return Yes for belonging to NIDILRR. The ones that don't match either list should still return no.

I hope this makes more sense! Thanks again for the help.
 
Upvote 0
birbs said:
I have a spreadsheet with a column of names, email addresses and organizations. I want Excel to find each name in a column on a separate sheet, find its organization, and return yes if organization contains specific text.

For example:

Sheet 1

NameemailOrganization
John Doejohndoe@email.comACME Incorporated
Jane Pateljanepatel@email.comGoogle
Melissa Williamsmelissawilliams@email.comFacebook
Eric Smithericsmith@email.comAmazon
Erica Williamsonericawilliamson@email.comACME Company

Sheet 2

NameemailACME?
John Doejohndoe@email.comYes
Jane Pateljanepatel@email.comNo
Melissa Williamsmelissawilliams@email.comNo
Eric Smithericsmith@email.comNo
Erica Williamsonericawilliamson@email.comYes


Thanks in advance!
Click to expand...

Here is how I would do this ...

First, sheet1 contains the cell values ...

sheet1.jpg


Then sheet2 which has the following formula applied to column D (ACME?) ...

sheet2.jpg



Regards,

Steve
 
Upvote 0
I don't understand why my original formula (with Peter's update) doesn't work for you. Given this Sheet1:

Book1
ABC
1Full name2 (sorted A-Z)EmailOrganization
2Abe Andrewsabeandrews@email.comOther – Please describe here:
3Barbara Bathbarbarabath@email.com
4Crispin ChuckyCrispinchucky@email.comNo/Don't know/Not sure
5Ellison Smithdavedrew@email.comNIDILRR project (i.e. you work on a NIDILRR award)
6Elise Williamseliseelsin@email.comNo/Don't know/Not sure
7Francis FudgeNo/Don't know/Not sure
8Gerald GerdNo/Don't know/Not sure`
Sheet1


Then Sheet2 looks like this:

Book1
ABC
1Name (sorted A-Z)emailNIDILRR?
2Allison Appleallisonapple@email.comNo
3Beth Beddenbethbedden@email.comNo
4Crispin Chuckycrispinchucky@email.comNo
5Dave Drewdavedrew@email.comNo
6Ellison Smithellisonemith@email.comYes
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IF(COUNTIFS(Sheet1!A2:A10,A2:A6,Sheet1!C2:C10,"*NIDILRR*"),"Yes","No")
Dynamic array formulas.


And gives you the results you expect. If this doesn't work for you, please explain why not.
 
Upvote 0
birbs said:
Hi, thanks so much for these, but these did not work for me.

I realized that the spreadsheets I shared as an example do not accurately reflect what I need help with, I apologize.

Let me try this again.

Sheet 1 (de-identified names and email addresses)

Full name2 (sorted A-Z)EmailOrganization
Abe Andrewsabeandrews@email.comOther – Please describe here:
Barbara Bathbarbarabath@email.com
Crispin ChuckyCrispinchucky@email.comNo/Don't know/Not sure
Ellison Smithdavedrew@email.comNIDILRR project (i.e. you work on a NIDILRR award)
Elise Williamseliseelsin@email.comNo/Don't know/Not sure
Francis FudgeNo/Don't know/Not sure
Gerald GerdNo/Don't know/Not sure`

Sheet 2:

Name (sorted A-Z)emailNIDILRR?
Allison Appleallisonapple@email.comNo
Beth Beddenbethbedden@email.comNo
Crispin Chuckycrispinchucky@email.comNo
Dave Drewdavedrew@email.comNo
Ellison Smithellisonemith@email.comYes


Sheet 1 contains registrants of a conference. Sheet 2 contains attendees of a conference. As you can see, there's more registrants than attendees, and the registration list has some blank entries. Furthermore, the attendee list does not directly match the registration list line by line, the names are mixed up. Although "Crispin Chucky" and "Ellison Smith" are contained on both lists, only "Ellison Smith" should return Yes for belonging to NIDILRR. The ones that don't match either list should still return no.

I hope this makes more sense! Thanks again for the help.
Click to expand...
From your description it appears that you have some unique characteristics that will likely require you create some VBA code to handle this. The reason I think this is because you have two sheets, each with a table ... but the size of the tables is likely to be different.

I created a workbook and here is what I did and it seems to work the way you want.

On sheet1, I created a table ... not a range ... that looks like this ...

1636813476757.png


Then, highlight all the entries in the Name column (only the entries, do not include the column header) and give that range the name, "registrants".

On sheet2, create another table ... again, not a range ... that looks like this ...

image2.jpg


As before, highlight the entries in the Name column (do not include the header) and give that range the name, "attendees"

Now create a macro ...

VBA Code: 
Sub checkregistration()

Dim c_att As Range
Dim c_reg As Range

For Each c_att In Range("attendees")
    For Each c_reg In Range("registrants")
        If c_att.Value = c_reg.Value Then
            If InStr(1, c_reg.Offset(0, 2).Value, "NIDILR") Then
                c_att.Offset(0, 2).Value = "Yes"
            Else
                c_att.Offset(0, 2).Value = "No"
            End If
        End If
    Next c_reg
Next c_att


End Sub

When you run the macro (sub), here is what is does:
  • Checks each cell in the attendee table to see if there is match in the registrants table
  • If a match is found, it then checks to see if the registrant is part of NIDILR
  • If the attendee is registered as NIDILR, then "YES" is will be inserted in the NIDILR column of the attendee table, otherwise, "NO".

Why use a table and not a plain vanilla range? By using a table, when you add an entry, the range names you defined will automatically expand with the table.

Hope this helps.
 
Last edited:
Upvote 0
bisel said:
From your description it appears that you have some unique characteristics that will likely require you create some VBA code to handle this. The reason I think this is because you have two sheets, each with a table ... but the size of the tables is likely to be different.
Click to expand...
:confused: Not sure why tables (whether equal-sized or not) would mean you needed vba instead of formulas.

The table on this sheet is "Table1"

birbs.xlsm
BCD
1
2Full NameEmailOrganization
3Abe Andrewsabeandrews@email.comOther – Please describe here:
4Barbara Bathbarbarabath@email.com
5Crispin ChuckyCrispinchucky@email.comNo/Don't know/Not sure
6Ellison Smithdavedrew@email.comNIDILRR project (i.e. you work on a NIDILRR award)
7Elise Williamseliseelsin@email.comNo/Don't know/Not sure
8Francis FudgeNo/Don't know/Not sure
9Gerald GerdNo/Don't know/Not sure`
10
11
Sheet1



birbs.xlsm
FGH
1
2
3
4NameemailNIDILRR?
5Allison Appleallisonapple@email.comNo
6Beth Beddenbethbedden@email.comNo
7Crispin Chuckycrispinchucky@email.comNo
8Dave Drewdavedrew@email.comNo
9Ellison Smithellisonemith@email.comYes
10
11
Sheet2
Cell Formulas
RangeFormula
H5:H9H5=IF(COUNTIFS(Table1[Full Name],[@Name],Table1[Organization],"*NIDILRR*"),"Yes","No")
 
Upvote 0
Peter_SSs said:
:confused: Not sure why tables (whether equal-sized or not) would mean you needed vba instead of formulas.

The table on this sheet is "Table1"

birbs.xlsm
BCD
1
2Full NameEmailOrganization
3Abe Andrewsabeandrews@email.comOther – Please describe here:
4Barbara Bathbarbarabath@email.com
5Crispin ChuckyCrispinchucky@email.comNo/Don't know/Not sure
6Ellison Smithdavedrew@email.comNIDILRR project (i.e. you work on a NIDILRR award)
7Elise Williamseliseelsin@email.comNo/Don't know/Not sure
8Francis FudgeNo/Don't know/Not sure
9Gerald GerdNo/Don't know/Not sure`
10
11
Sheet1



birbs.xlsm
FGH
1
2
3
4NameemailNIDILRR?
5Allison Appleallisonapple@email.comNo
6Beth Beddenbethbedden@email.comNo
7Crispin Chuckycrispinchucky@email.comNo
8Dave Drewdavedrew@email.comNo
9Ellison Smithellisonemith@email.comYes
10
11
Sheet2
Cell Formulas
RangeFormula
H5:H9H5=IF(COUNTIFS(Table1[Full Name],[@Name],Table1[Organization],"*NIDILRR*"),"Yes","No")
Click to expand...

That works too.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Formula that returns row containing a specific value from a cell
Replies
2
Views
325
Fluff
Fluff
J
  • Solved
count the number of cells with specific texts and also have a value greater than 0 in an adjacent cell
Replies
1
Views
3K
jasonb75
J
S
  • Question Question
Finding next and previous value of a column relative to a specific ID column
2
Replies
16
Views
836
Fluff
Fluff
I
  • Question Question
Need to copy specific string of text from column into another cell
Replies
5
Views
892
shijith
shijith

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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