Vlookup Handle zero or blank
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Vlookup Handle zero or blank
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
=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.
Dave.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup Handle zero or blank
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")))))
=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
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Vlookup Handle zero or blank
Sheeesh isn't the word Hans, this has been a very complicated project
However, I think it failed;
However, I think it failed;
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Vlookup Handle zero or blank
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
All sensitive info removed.
Thanks Hans
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Vlookup Handle zero or blank
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
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.
Dave.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup Handle zero or blank
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"))))))
=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
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Vlookup Handle zero or blank
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.
??
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.
Dave.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Vlookup Handle zero or blank
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?
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?
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup Handle zero or blank
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.
=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
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Vlookup Handle zero or blank
Yes it should... how did I not see that !!!!!!!
Thanks Hans, I was on the right track...
Thanks Hans, I was on the right track...
Cheers ...
Dave.
Dave.