I have two question as per attachment.
Question 1:
How to return value that will only vlookup if the condition is true, else return 0 if the condition is false. i try multiple solution but it didn't work.
Question 2:
The formula that I use in column E didn't return the value in column A when Column C is zero instead it is return the value False.
How to correct this.
Thank you.
Khalil
For row 2 probably something as: =AND(A2="Permanent",B2<>0,B2<>4)
I think that'll do.
Hi Khalil
Refer attached.
Q1
Frans have already given you the first answer.
As for the lookup, I believe you are comparing more than 1 column (i.e. column B and D) as the lookup table is a cross-tab.
If that is so, then you will need to modify the lookup table (see my example attached). In your example, all the values in the same row in the lookup table is the same e.g. 7% for row 2 etc but if you have different values, then my example will work as well.
Q2
I believe your last IF is missing an argument (in red).
=IF(AND(C2<>0,A2<B2,D2<B2),D2,IF(AND(C2<>0,A2<B2,D2>=B2),B2,IF(AND(C2<>0,A2>=B2,D2>=A2),0,A2)))
Hope this helps.
Sunny
