vlookup problem, if statement

    Hi,


    I am using a lookup which works fine to a point....


    =VLOOKUP(J8;'Weeks Due'!A$2:AU$51;F8+1;FALSE)


    J8 referes to "Projected Age Years"
    F8 refers to "Projected Service Years"



    however i am getting some funny results if the "F8+1 = 17"


    this is all to do with some strange spreadsheet to calculate bonus payment, however you only get paid the bonus if more than one years service and over the age of 17...


    how can i modify the vlookup to read, "=vlookup...work your magic, however if the f8 result=17 then "0")


    hope that makes some sense


    Andy

    Re: vlookup problem, if statement


    Try something along the lines of:


    =IF(AND(J8>17,F8>1),VLOOKUP(J8,'WEEKS DUE'!$A$2:$AU$52,F8+1,FALSE),"Person under 17 or Less than 1 year")


    This allows the VLOOKUP to run only if the age and years requirements are met. Also, make sure you are using hard references correctly if you are "dragging" the formula across the sheet. You original references in the VLOOKUP WILL CHANGE if they don't have the $ before your cell reference column and row.

    Re: vlookup problem, if statement


    ok after much thought, what i need this formula to read is much simpler than my stupid original mindset....


    If f8 =0 then 0 else run vlookup, how do i type this please?

    Re: vlookup problem, if statement


    =IF(F8=0,0,VLOOKUP([Look-up Value],[Array Range],[# of Column In Range With Desired Value],False))

    Re: vlookup problem, if statement


    ok i have tried the following....


    =IF(F8=0,0,VLOOKUP( WEEKS DUE'!$A$2:$AU$52,F8+1,FALSE),"Person under 17 or Less than 1 year")


    however i get an error arounf the 0,0,VLOOKUP area...


    Sorry for being a pain here

    Re: vlookup problem, if statement


    It's not a problem, but understand, I'm shooting blind.


    When I have formula issues, I tend to simplify my formulas and then build them up. By that I mean, first get your VLOOKUP to work properly (while ignoring the if limitation).


    See if the attached sheet helps at all. If possible, attach a sample of your workbook with any sensitive information removed and I might be able help more.
    forum.ozgrid.com/index.php?attachment/42190/

    Re: vlookup problem, if statement


    formula for K12:


    =IF(J12<=17,0,VLOOKUP(J12,'Redundancy Weeks Due'!$A$2:$AU$51,'Main Sheet'!F12+1))

    Regards,


    WidgetWonka
    Puuuureeee Imagination

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login