+ Reply to Thread
Results 1 to 4 of 4

Formula to match data in two cells and if another cell has specific text, enter value

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    182

    Formula to match data in two cells and if another cell has specific text, enter value

    Hello, I am trying have my spreadsheet compare two columns and if the data matches than in another column enter a checkmark if a cell contains a specific value.

    On my spreadsheet, "student worker list" will have a list of students that are assigned a number. I want a formula in "student worker list" in the L column to look at "student worker pay" to search column A for the matching number and then if Cell E has the word "August" to enter a checkmark in the corresponding L cell. "Student worker pay" may have the number listed more than once, because every month when they submit their timesheet, it will show, just the month changes. So I would duplicate this formula in "student worker list" in K through V with the appropriate month.

    TO simplifiy, I just want a checkmark in column K thru V if that particular student submitted their timesheet for that month based on the data in "student worker pay" . So K = June, L = August, M = September, etc.

    Any suggestions.
    13-14 budget.xlsm

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formula to match data in two cells and if another cell has specific text, enter value

    First, in K1 put 6/1/2013 and in L1 put 7/1/2013.

    Next, highlight both cells and hit Ctrl+1 to bring up Format. Choose custom format, and just put mmmm on the line. This will let us store an actual date we can reference while still displaying as nothing but a month name.

    Then highlight both cells and drag right to duplicate format and produce sequential first dates of month.

    Next, plop this into K2:

    =IF(SUMPRODUCT(('Student Worker Pay'!$A$2:$A$86=$A2)*('Student Worker Pay'!$E$2:$E$86='Student Worker List'!K$1))>0,CHAR(252),CHAR(120))

    and copy down and over as far as needed.

    This will reference the Student ID and match the date in row 1. If it finds any matches, it puts character 252 which is a checkmark in Wingdings. If it doesn't find a match, it puts character 120, which is the little X box.


    Here the finished product, in case you get stuck.

    budgets.xlsm
    Last edited by daffodil11; 09-20-2013 at 03:20 PM.

  3. #3
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    182

    Re: Formula to match data in two cells and if another cell has specific text, enter value

    You are amazing and a life saver. Thank you

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formula to match data in two cells and if another cell has specific text, enter value

    Thanks for the feedback, glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Match text in A1 to a part of a text string in B1 and enter data D1
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-30-2013, 10:16 PM
  2. [SOLVED] Need formula to enter specific text if a column contains other specific text
    By sugarmags74 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2012, 12:09 AM
  3. [SOLVED] Counting Cells that includes exact match and also if a cell includes specific text
    By smclachlan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-19-2012, 07:42 PM
  4. [SOLVED] Match data in cells then enter date in empty cell
    By MyComputerGuy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2012, 08:16 PM
  5. Enter text into a specific cell using a userform
    By Cushionsupplier in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2010, 05:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1