=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)

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.

First, this is incorrect: >"0" By using the quotes around the zero, you asking Excel to check for a

textzero, 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_falseargument 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_falseargument might return a #N/A error.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History