+ Reply to Thread
Results 1 to 4 of 4

Looking for a value using multiple criteria then....

  1. #1
    Registered User
    Join Date
    06-22-2015
    Location
    Northants, England
    MS-Off Ver
    2010
    Posts
    2

    Looking for a value using multiple criteria then....

    Fairly new to this, hoping you guys/gals can help, struggling to work out how to do this.

    I want to look for a value in a column using multiple criteria and if it exists the do something if not do something else.

    For example, does the value "XYZ" exist in column C where the value in column B is 01/01/2019 and the value in column A is 12345, if yes then say 'complete' otherwise lookup an alternative value using vlookup.

    Hope that makes some sense to someone and appreciate the feedback.

  2. #2
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,363

    Re: Looking for a value using multiple criteria then....

    from your limited description, this would work for at least the first part of your "multiple criteria ... if it exists" but you don't define your lookup OR vlookup, so that is left for you to fill in...
    =IF(COUNTIF(A:A,12345)+COUNTIF(C:C,"xyz")+COUNTIF(B:B,DATE(2019,1,1))>=3,"complete",vlookup(…))

    IF however, your 12345 AND xyz AND date 1/1/2019 are all in the same row then this shorter version of the countifS would work.
    =if(COUNTIFS(B:B,DATE(2019,1,1),A:A,12345,C:C,"xyz")>=3,"complete",vlookup(…)

    AND, you may need to change out the commas to semicolons depending on your settings.
    Last edited by Sambo kid; 03-13-2019 at 05:15 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    06-22-2015
    Location
    Northants, England
    MS-Off Ver
    2010
    Posts
    2

    Re: Looking for a value using multiple criteria then....

    Thx for your response. Apologies if it was a bit vague. I have inserted some sample date to see if the answer is any different.

    Starting with 01/01/2019, if the value BH exists in column B for that date using the multiple criteria of A & C the result should be 'some text' otherwise lookup another value. So, for the ID C0953746 for the 01/01/2019 there is a value of BH in column B therefore the output should simply be 'some string text', for the 02/01/2019 for the ID C0953746 where there is no value of BH present in column B the output should be the value in column D for the item SHIFT, therefore C0953746 for the 02/01/2019 would be 08:15.

    A B C D
    C0953746 SHIFT 01/01/2019 08:15
    C0953746 FB 01/01/2019 00:15
    C0953746 LN 01/01/2019 00:45
    C0953746 LB 01/01/2019 00:15
    C0953746 BH 01/01/2019
    C0953746 SHIFT 02/01/2019 08:15
    C0953746 MISC 02/01/2019 00:30
    C0953746 LB 02/01/2019 00:20
    C0953746 EF 02/01/2019 02:15
    C0953746 MISC 02/01/2019 00:30
    C0953746 CO 02/01/2019 00:15
    C0953746 MISC 03/01/2019 00:30
    C0953746 SHIFT 03/01/2019 08:15
    C0953746 LB 03/01/2019 00:20
    C0953746 CO 03/01/2019 00:15
    C0953746 EF 03/01/2019 02:15

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,869

    Re: Looking for a value using multiple criteria then....

    Hello Steve and Welcome to Excel Forum.
    The formula I am proposing is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note the lookup ID is in column F and the lookup date is in column G.
    For future reference you will likely get faster/better responses if you upload a file rather than putting data in a post. To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick reply window and then scroll down to Manage Attachments to open the upload window.
    1. Make sure there is just enough data to demonstrate your need.
    2. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
    3. Make sure your desired results are shown, mock them up manually if necessary.
    4. Remember to desensitize the data.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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