+ Reply to Thread
Results 1 to 2 of 2

IF Function with VLookup

  1. #1
    Registered User
    Join Date
    07-06-2017
    Location
    Florida, USA
    MS-Off Ver
    2010
    Posts
    1

    IF Function with VLookup

    I am trying to write a small formula to calculate the bonus potential based on hitting my targeted sales parameters:
    Salary.JPG

    Depending on how much sales are exceeded by there is a corresponding chart that shows the payout potential:
    table.JPG

    All that needs to be done is input your Salary (B1) and your sales to plan (B9) and your bonus payout should be calculated in B12.

    I have a basic IF Function that is using VLookup to reference the table, but I am having issues calculating the payout. Whatever is reference from column B on the payout worksheet would be multiplied by number in the calculator worksheet in cell (B6) in this scenario the $700 figure.

    This is my formula/Function:

    =IF(B9>=99%, VLOOKUP(B9,'Earn over Plan'!$A$3:$B$52,2,FALSE*Calculator!B6), FALSE)

    I believe my issues lies in the "FALSE*Calculator!B6" portion of the VLOOKUP function. Where FALSE is the percentage value return: 101%, 102%, etc., etc., and then multiply that by the value in cell B6 to give me the correct dollar value.

    What am I doing wrong? It's late so I'm tired, maybe it's a simple fix and I am just missing it. Any help would be most welcome. Thanks!

    -M

  2. #2
    Registered User
    Join Date
    05-21-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    25
    Evening buddy,

    Looking at that its a simple misplacement of a bracket. Move the close bracket from

    =IF(B9>=99%, VLOOKUP(B9,'Earn over Plan'!$A$3:$B$52,2,FALSE*Calculator!B6), FALSE)

    To

    =IF(B9>=99%, VLOOKUP(B9,'Earn over Plan'!$A$3:$B$52,2,FALSE)*Calculator!B6,FALSE)

    Also as a quick excel tip, instead of writing false everytime you can simply put 0

    Thanks
    Daniel
    Last edited by AliGW; 07-07-2017 at 01:04 AM. Reason: Unnecessary quotation removed.

+ 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. [SOLVED] Trying to Multiply the result of an IF function combined with a VLOOKUP function
    By ucca in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-28-2018, 02:32 AM
  2. [SOLVED] VLOOKUP formula or VLOOKUP worksheet.function
    By Crispy85 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-09-2017, 09:40 AM
  3. Replies: 2
    Last Post: 01-15-2014, 11:40 PM
  4. Need help nesting an index/match function within a Vlookup function.
    By Christopher135 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2013, 06:16 PM
  5. Worksheet function Vlookup and VLOOKUP return different results
    By zandero in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2010, 08:24 AM
  6. Replies: 1
    Last Post: 12-02-2005, 09:35 AM
  7. [SOLVED] vlookup function-Can vlookup command find the data from the 5 sheets.
    By Rishab shah in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1