# i NEED TO GET ZERO, IF THE LOOKUP VALUE IS NOT IN THE CELL February 22, 2012 at 02:15:37
Specs: Windows 7
 =IF(ISNA(VLOOKUP(E36,'INCENTIVE SLAB MASTER - B'!\$M\$58:\$N\$68,2,FALSE)),"0",IF(AND(VLOOKUP(A36,'ATTENDANCE-B'!\$J\$3:\$K\$300,2,FALSE))>"0",VLOOKUP(A36,'ATTENDANCE-B'!\$J\$3:\$K\$300,2,FALSE)<=5,VLOOKUP(E36,'INCENTIVE SLAB MASTER - B'!\$H\$55:\$J\$68,2,FALSE))) - 1ST IT WILL LOOK FOR THE GROUP, IF E3 IS THERE IN THE GROUP, THEN IF A36 >0 AND <=5, oNE VALU FROM TABLE TO BE RETURNED AS ANSWER, OTHERWISE ZERO,IN MY CASE IT IS COMMING AS #N/A ( IF NO A36 AVBL IN THE TABLE) See More: i NEED TO GET ZERO, IF THE LOOKUP VALUE IS NOT IN THE CELL

#1 February 22, 2012 at 05:50:51
 Please do not post in all upper case.Upper case is the internet equivalent of yelling and no one likes to be yelled at.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2 February 22, 2012 at 06:42:36
 First, this is incorrect: >"0"By using the quotes around the zero, you asking Excel to check for a text zero, not the number 0. This may or may not impact your outcome, depending on how Excel evaluates the formula, but as a general rule when working with numbers in Excel, never use quotes around them.Second re: "IF E3 IS THERE IN THE GROUP..."I don't see E3 anywhere in your formula, so I am going to assume that that is a typo and that you really meant E36.Third, as far as getting #N/A as an result, you answered your own question in your post.You said: "IN MY CASE IT IS COMMING AS #N/A ( IF NO A36 AVBL IN THE TABLE)"If the IF function reaches the value_if_false argument and tries to evaluate the AND function, the first thing it is going to do is evaluate the VLOOKUPs, in order. If any of them return a #N/A error, then the entire formula will return a #N/A error.Just as you used the ISNA function to handle the initial logical test of the IF fucntion, you need to deal with the possibility that the value_if_false argument might return a #N/A error.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions 