+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Combining vlookup and if statements to return multiple criteria

  1. #1
    Registered User
    Join Date
    09-07-2011
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Question Combining vlookup and if statements to return multiple criteria

    I have a spreadsheet which filters on various criteria based on location and contract renewal dates etc.
    Previously, I had a simple if statement to filter on usage criteria: =IF(AY2>=2000, "GOOD", IF(AY2>=1000, "OK", IF(AY2>=0.1, "BAD", IF(AY2 >0.1, "BAD", "Nil usage" ) ) ) )

    (not pretty but it worked)

    I now find I need to create a more accurate filter based on other numerical criteria i.e. more than 500 hours and more than 5 tasks set and Data sent in would return GOOD
    I attach an example of what I want with the data headings and rules:

    HTML Code: 
    Client Ref:	name	Data sent?	HoursQ1	Logins	Groups	Tasks	Status:
    1	Brown	Y	1200	6	6	17	GOOD
    2	Jones	N	200	2	8	7	BAD
    3	Smith	N	75	4	2	15	BAD
    4	David	Y	40	2	6	6	BAD
    5	Jupp	Y	160	0	3	12	BAD
    6	Fine	Y	245	9	4	14	OK
    							
    							
    RULES:							
    If Data sent is "N" then "Status" is always "BAD" regardless of the other conditions							
    							
    Good:			>500	=>5	=>5	=>3	
    OK:			=>100	=>3	=>3	=>2	
    BAD:			=<99	=<2	=<2	=<1	
    I also attach an example sheet as above. I hope someone can help.

    Thanks
    Simon
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Combining vlookup and if statements to return multiple criteria

    maybe
    =IF(C2="n","bad",IF(AND(D2>500,E2>=5,F2>=5,G2>=3),"good",IF(AND(D2>100,E2>=3,F2>=3,G2>=2),"ok","bad")))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    09-07-2011
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Combining vlookup and if statements to return multiple criteria

    Great, works well. Just one thing to finish it off.
    I now find that each week, I enter new data and the number of records may vary, there maybe some blank records at the bottom which will be marked as BAD using this formula. Is there any way I can make those cells return nothing so when I filter them, blank records are not shown or add a blank criteria? i.e. "" or NO DATA
    Hope this makes sense and thanks for the quick response.

    Just to be even more adventurous.... I have now referenced the cells along the lines of Hgood, Hok, Lgood etc. It got me thinking. Each quarter, I will need to edit the data table as the figures are cumulative. Wouldn't it be great if I could use the date function to change the values of the referenced cells using the TODAY() function and setting up a table showing the start and end dates of each quarter. Each referenced cell would then change automatically when the new quarter started.

    Just a thought....
    Last edited by rnomis; 09-07-2011 at 06:47 PM. Reason: additional information

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Combining vlookup and if statements to return multiple criteria

    add another if for if(c2="","" or whatever cell you choose

+ 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