+ Reply to Thread
Results 1 to 6 of 6

IF Function with VLOOKUP Not Working

  1. 11-25-2014, 01:32 AM #1
    lanny420
    lanny420 is offline
    Registered User
    Join Date
    05-01-2014
    Location
    Richmond, Virgina
    MS-Off Ver
    Excel 2007
    Posts
    2

    IF Function with VLOOKUP Not Working

    Hello,

    I have a spreadsheet, which contains employee anniversary date informaition. What I'm trying to accomplish is to populate the cell with the employee's new accrual rate based on the calculated years of service and whether or not they are in the classified service. In row L18 I have the following formula entered, but it is generating #N/A. Any assistance that can be provided will be greatly appreciated! Many thanks in advance!

    =IF(M18="Classified",VLOOKUP(K18,Sheet2!A3:B7,1,FALSE),VLOOKUP(Sheet2!E3:F5,1,FALSE))

    File is attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-25-2014, 01:50 AM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: IF Function with VLOOKUP Not Working

    Your formula was attempting to VLOOKUP numeric values in a text list. Using just the first numbers in the ranges will give a start number for the range of values you seek. The next start number implicitly ends the previous range and begins the next. So, I changed Sheet2 to this:
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    1
    		 CLASSIFIED ACCRUAL RATES UNCLASSIFIED ACCRUAL RATES
    2
    		 Years of Service Accrual Rate Years of Service Accrual Rate
    3
    1
    3.7
    1
    120
    4
    5
    4.6
    5
    160
    5
    10
    5.5
    10
    200
    6
    15
    6.6
    7
    20
    7.4
    8


    and your formula to this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    which returns the Accrual rate from the second column of your Classified lookup table.
    It all seems to work fine.
    Attached Files Attached Files
    Last edited by FlameRetired; 11-25-2014 at 02:00 AM.
    Reply With QuoteRegister To Reply
  3. 11-25-2014, 02:21 AM #3
    samba_ravi
    samba_ravi is online now
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,913

    Re: IF Function with VLOOKUP Not Working

    Quote Originally Posted by FlameRetired View Post
    Your formula was attempting to VLOOKUP numeric values in a text list. Using just the first numbers in the ranges will give a start number for the range of values you seek. The next start number implicitly ends the previous range and begins the next. So, I changed Sheet2 to this:
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    1
    		 CLASSIFIED ACCRUAL RATES UNCLASSIFIED ACCRUAL RATES
    2
    		 Years of Service Accrual Rate Years of Service Accrual Rate
    3
    1
    3.7
    1
    120
    4
    5
    4.6
    5
    160
    5
    10
    5.5
    10
    200
    6
    15
    6.6
    7
    20
    7.4
    8


    and your formula to this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    which returns the Accrual rate from the second column of your Classified lookup table.
    It all seems to work fine.
    Lookup Value was missed in the 2nd Vlookup formula
    =IF(M18="Classified",VLOOKUP(K18,Sheet2!A3:B7,2,FALSE),VLOOKUP(K18,Sheet2!E3:F5,2,FALSE))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  4. 11-25-2014, 03:22 AM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: IF Function with VLOOKUP Not Working

    Quote Originally Posted by nflsales View Post
    Lookup Value was missed in the 2nd Vlookup formula
    =IF(M18="Classified",VLOOKUP(K18,Sheet2!A3:B7,2,FALSE),VLOOKUP(K18,Sheet2!E3:F5,2,FALSE))
    So it was. Thanks for the intercept.
    Reply With QuoteRegister To Reply
  5. 11-25-2014, 01:09 PM #5
    lanny420
    lanny420 is offline
    Registered User
    Join Date
    05-01-2014
    Location
    Richmond, Virgina
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: IF Function with VLOOKUP Not Working

    FlameRetired,

    A million thanks! Your correction to the formula and table worked wonderfully!!!!!
    Reply With QuoteRegister To Reply
  6. 11-26-2014, 07:31 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: IF Function with VLOOKUP Not Working

    You're welcome. Glad it worked.
    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. [SOLVED] VLOOKUP function not working :/
    By Spicey_888 in forum Excel General
    Replies: 6
    Last Post: 09-27-2014, 10:39 PM
  2. Vlookup inside IF function not working
    By Ruby91 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-07-2014, 10:30 AM
  3. VLOOKUP Function not working
    By exposure31 in forum Excel General
    Replies: 2
    Last Post: 08-28-2012, 04:32 PM
  4. Vlookup function not working
    By rahulkrishnanr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2012, 06:53 AM
  5. Excel 2007 : VLookup Function Not Working
    By LAB2007 in forum Excel General
    Replies: 4
    Last Post: 06-30-2010, 03:23 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1