+ Reply to Thread
Results 1 to 5 of 5

3 different formulas based on VLOOKUP result

  1. 11-28-2018, 08:21 AM #1
    mr.mike.svk
    mr.mike.svk is offline
    Registered User
    Join Date
    11-28-2018
    Location
    Slovaki
    MS-Off Ver
    2010
    Posts
    2

    3 different formulas based on VLOOKUP result

    Hello,

    I am trying to solve such interesting challenge.

    I need to call result and based on the result I need to apply one of three different formulas

    I am calling the result via VLOOKUP - VLOOKUP(B2;BP!B:R;17;FALSE)

    If the result is below 120, I need this formula- IF(SUM(COUNTIF(D18;">0");COUNTIF(E18;">0"))>=1;"OK";"PROBLEM")
    If the result is between 121 and 200, I need this formula - IF(SUM(COUNTIF(D18;">0");COUNTIF(E18;">0"))>=2;"OK";"PROBLEM")
    If the result is over 201, I need this formula - IF(SUM(COUNTIF(F18;">0");COUNTIF(G18;">0"))>=3;"OK";"PROBLEM")

    Please advise how to put a formula together

    Regards,
    Michal
    Reply With QuoteRegister To Reply
  2. 11-28-2018, 08:39 AM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,792

    Re: 3 different formulas based on VLOOKUP result

    can you use a helper coloumn and then a simple nested IF

    otherwise a NESTED IF with a vlookup()

    however what are you using countif () for - you are only testing 1 cell

    is this
    SUM(COUNTIF(D18;">0");COUNTIF(E18;">0"))>=1
    simply saying
    OR ( D18 > 0, E18 > 0 )

    if so then you could probably simplify that formula a lot

    i wait for your reply on the helper column for the vlookup value and using OR()
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 11-28-2018, 09:07 AM #3
    Sam Capricci
    Sam Capricci is online now
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,504

    Re: 3 different formulas based on VLOOKUP result

    Wayne asked some good questions and without answers as yet, I'm wondering if this would work for you...
    =IF(AND(VLOOKUP(B2;BP!B:R;17;FALSE)<120;D18>0;E18>0;SUM(D18;E18)>=1);"OK";IF(AND(VLOOKUP(B2;BP!B:R;17;FALSE)<=200;D18>0;E18>0;SUM(D18;E18)>=2);"OK";IF(AND(VLOOKUP(B2;BP!B:R;17;FALSE)>201;F18>0;G18>0;SUM(F18;G18)>=3);"OK";"PROBLEM")))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 11-28-2018, 11:03 AM #4
    mr.mike.svk
    mr.mike.svk is offline
    Registered User
    Join Date
    11-28-2018
    Location
    Slovaki
    MS-Off Ver
    2010
    Posts
    2

    Re: 3 different formulas based on VLOOKUP result

    Hi etaf,

    thank you for your advise, I tried NESTED IF and it works.

    Regards,
    Michal
    Reply With QuoteRegister To Reply
  5. 11-28-2018, 11:19 AM #5
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,792

    Re: 3 different formulas based on VLOOKUP result

    you are welcome
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formulas as a result of Vlookup
    By xfish in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-26-2016, 12:11 PM
  2. [SOLVED] to use IF & SUMIFS formulas to get a result based on dates
    By Nitinkumar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-03-2015, 02:06 PM
  3. Result of two formulas (vlookup and Color) into one CELL
    By cybertaurean in forum Excel General
    Replies: 4
    Last Post: 10-10-2012, 03:32 AM
  4. [SOLVED] Populate a Cell with text based on the result of 2 seperate formulas
    By Medwards73 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 08:56 AM
  5. Changing texbox value based on VLOOKUP result
    By anar_baku in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2011, 07:02 AM
  6. Sumif based on Vlookup Result
    By ferris in forum Excel General
    Replies: 3
    Last Post: 09-10-2009, 03:44 AM
  7. vlookup based on random result returns incorrect result
    By rickat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2005, 09:20 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1