Vlookup Handle zero or blank

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Vlookup Handle zero or blank

Post by D Willett »

With the below formula, if there is an empty cell or it contains a zero it still returns the lowest value from a number of cells from the different tabs (sheets), I'm guessing the IFERROR can only handle values if they exist. Is this the case and can the formula be edited to handle Null or 0 ?

=IFERROR(VLOOKUP($A2,'Salary Calculation INC 1.5'!$A$3:$AX$52,3,FALSE),IFERROR(VLOOKUP($A2,'Salary Calculation Max 1.25'!$A$3:$AX$31,3,FALSE),IFERROR(VLOOKUP($A2,'Salary Calculation £20 Capped'!$A$3:$AX$19,3,FALSE),IFERROR(VLOOKUP($A2,'Admin 45 hrs week'!$A$3:$AX$19,3,FALSE),IFERROR(VLOOKUP($A2,'Salary Calculation No Overtime'!$A$3:$AX$25,3,FALSE),"Not Found")))))

Cheers
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Vlookup Handle zero or blank

Post by HansV »

Sheesh! Does this do what you want?

=IF(IFERROR(VLOOKUP($A2,'Salary Calculation INC 1.5'!$A$3:$AX$52,3,FALSE),0)>0, VLOOKUP($A2,'Salary Calculation INC 1.5'!$A$3:$AX$52,3,FALSE), IF(IFERROR(VLOOKUP($A2,'Salary Calculation Max 1.25'!$A$3:$AX$31,3,FALSE),0)>0, VLOOKUP($A2,'Salary Calculation Max 1.25'!$A$3:$AX$31,3,FALSE), IF(IFERROR(VLOOKUP($A2,'Salary Calculation £20 Capped'!$A$3:$AX$19,3,FALSE),0)>0, VLOOKUP($A2,'Salary Calculation £20 Capped'!$A$3:$AX$19,3,FALSE), IF(IFERROR(VLOOKUP($A2,'Admin 45 hrs week'!$A$3:$AX$19,3,FALSE),0)>0, VLOOKUP($A2,'Admin 45 hrs week'!$A$3:$AX$19,3,FALSE), IF(IFERROR(VLOOKUP($A2,'Salary Calculation No Overtime'!$A$3:$AX$25,3,FALSE),0)>0, VLOOKUP($A2,'Salary Calculation No Overtime'!$A$3:$AX$25,3,FALSE), "Not Found")))))
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Vlookup Handle zero or blank

Post by D Willett »

Sheeesh isn't the word Hans, this has been a very complicated project :groan:

However, I think it failed;
ScreenHunter_261.jpg
You do not have the required permissions to view the files attached to this post.
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Vlookup Handle zero or blank

Post by HansV »

Could you attach a sample workbook? :blackteeth:
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Vlookup Handle zero or blank

Post by D Willett »

Attached Hans, it's quite complicated but the formulae are on sheet "Final Payments" in column(s) B, D, E, F, G,H, I, K, N. The rows go to 100 to allow for more staff so I wanted to pull the formulas down and then lock it all up, that would allow payroll staff to add staff and the formula be available without unprotecting it again.

All sensitive info removed.

Thanks Hans
You do not have the required permissions to view the files attached to this post.
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Vlookup Handle zero or blank

Post by HansV »

Thanks. What do you want the formulas to return?
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Vlookup Handle zero or blank

Post by D Willett »

The formula returns exactly what it should as it is.
With the example sent earlier, if the formula is pulled down from cell A18 ( and the other columns with formula) down to 100 it should return nothing or a blank cell. Whe payroll add a member of staff to the bottom of the list the formulae are present and do their stuff.

If that makes sense Hans.

Thanks Again
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Vlookup Handle zero or blank

Post by HansV »

Ah, I understand now. Use formulas like this:

=IF(A2="","",IFERROR(VLOOKUP($A2,'Salary Calculation INC 1.5'!$A$3:$AX$8,3,FALSE),IFERROR(VLOOKUP($A2,'Salary Calculation Max 1.25'!$A$3:$AX$5,3,FALSE),IFERROR(VLOOKUP($A2,'Salary Calculation £20 Capped'!$A$3:$AX$19,3,FALSE),IFERROR(VLOOKUP($A2,'Admin 45 hrs week'!$A$3:$AX$19,3,FALSE),IFERROR(VLOOKUP($A2,'Salary Calculation No Overtime'!$A$3:$AX$5,3,FALSE),"Not Found"))))))
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Vlookup Handle zero or blank

Post by D Willett »

:thumbup:

Thank you Hans, works a dream
Cheers ...

Dave.

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Vlookup Handle zero or blank

Post by D Willett »

Quick question Hans.

Attempting a custom sort, I find the blank cells which contain the formula's to be at the top of visible data. Reading up about this it seems to be because we are adding a text value of "" so the cell is not truly empty.

Looking at the formula I can replace the 2nd quotations to "Zero" which puts those cells back to the bottom.

=IF($A62="","Zero",IFERROR(VLOOKUP($A62,'Salary Calculation INC 1.5'!$A$3:$AX$52,3,FALSE),IFERROR(VLOOKUP($A62,'Salary Calculation Max 1.25'!$A$3:$AX$31,3,FALSE),IFERROR(VLOOKUP($A62,'Salary Calculation £20 Capped'!$A$3:$AX$19,3,FALSE),IFERROR(VLOOKUP($A62,'Admin 45 hrs week'!$A$3:$AX$19,3,FALSE),IFERROR(VLOOKUP($A62,'Salary Calculation No Overtime'!$A$3:$AX$25,3,FALSE),"Not Found"))))))

I am just wary of breaking the formula though, does it look a safe bet to do this Hans and add a conditional format to put the cell text to White and therefore fooling the user.

??
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Vlookup Handle zero or blank

Post by HansV »

That should be OK.
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Vlookup Handle zero or blank

Post by D Willett »

Thanks Hans
Cheers ...

Dave.

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Vlookup Handle zero or blank

Post by D Willett »

Hi Hans
Huston, we have a problem.

Would you mind taking a look at two formula's on the sheet 'Salary Calculation INC 1.5' please.
Column AW should:
=IF(MIN($F3,$M3,$T3,$AA3<C$60),$AK3,$AO3+$AQ3+$AS3+$AU3)
(If F3,M3,T3,AA3 < 40 then give me the value from AK3, else give me the value of AO3+AQ3+AS3+AU3)

Column AX should:
=IF(MIN($F3,$M3,$T3,$AA3<C$60),0,$AP3+$AR3+$AT3+$AV3)
(If F3,M3,T3,AA3 < 40 then give me 0, else give me the value of AP3+AR3+AT3+AV3)

If you look at Payroll ID 457000 (row 24), the employee has done the following hrs: 53.5, 36.75, 49.75, 42.5.
In week 2 he has not acheived the basic 40 hrs so column AW should read 22.5 and column AX should read 0

Does this make sense, and its payroll week?
tmp.xlsx
You do not have the required permissions to view the files attached to this post.
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Vlookup Handle zero or blank

Post by HansV »

Shouldn't it be

=IF(MIN($F3,$M3,$T3,$AA3)<C$60,$AK3,$AO3+$AQ3+$AS3+$AU3)

and

=IF(MIN($F3,$M3,$T3,$AA3)<C$60,0,$AP3+$AR3+$AT3+$AV3)

i.e. different placement of the closing parenthesis of the MIN function.
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Vlookup Handle zero or blank

Post by D Willett »

Yes it should... how did I not see that !!!!!!!

Thanks Hans, I was on the right track... :flee:
Cheers ...

Dave.