 # 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/

## Files

• Re: vlookup problem, if statement

You didn't specify a lookup value in your last VLOOKUP.

Regards,

WidgetWonka
Puuuureeee Imagination

• Re: vlookup problem, if statement

forum.ozgrid.com/index.php?attachment/42191/

In the strippeddown example you will see the problem is in Row k12 the figure displays 17.0 whereas as it should actually be zero given the employee has worked less than 1 year.

I therefore need this to read zero, else do the lookup

## Files

• 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!