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

Report •

#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


Ask Question