if and vlookup combined formula puts out an error

[venkatesh]

Uploaded file refers to a data table that contains employee category and the conditions upon which they are remunerated. I want a formula in column I to satisfy all the conditions set out in column F; i.e supposing employee category MR is remunerated only 40% and there are 20 MR employees then the answer would be 20*40%. Similarly if MR category is remunerated in USD the anticipated answer would be 20(20 employees are remunerated in USD). It will go on for Rs. However if I attempt to combine all these conditions in a formula with IF and vlookup, the result turns out to be either Value or #NA error.

Please help me in solving this

Venkatesh

 

[Somendra Misra]

Hi venkatesh,

Just un merge the cell range F3:G5, and see whether you are getting correct result or not.

Regards,

 

[json]

your first condition references F31. "IF(F31>0%". however, F31 contains text... so thats why it returns the value error, because it does not have a valid value that is needed.

i think all you'll have to do us rearrange the functions so that the "IF(F31>0%" part is at the end...

EDIT: Correction: the Vlookup that is being used is causing the function to fail for any of the percentages. since this is your first condition, if the value in F is not found through the vlookup, it will result in N/A#, not as "False". Since this part does not return the "False", it does not continue through the rest of the IFFALSE argument.

 

[SirJB7]

Hi, venkatesh!
What are the possible values for column F and what should be done for each one of them? I.e., explain in detail your logic.
Regards!

 

[json]

try this:
=IF(ISNUMBER(F10)=TRUE,(G10+H10)*F10,IF(F10="RS",G10+H10,IF(F10=VLOOKUP(F10,$F$3:$G$5,1,0)=TRUE,G10+H10,0)))


Breakdown:
i start the function off by determining if the content of F10 is a number or not. If it is (a % in this case), it adds G10+H10 and then multiplies that sum by the value of F10. IF is finds that F10 is not a value, it determines if F10="RS". if that is found to be true, it adds G10+H10. if F10 does not equal "RS", i used your vlookup function with a twist. my last part will look for the value of F10 in F3:G5 and if it finds that value it will techincally return a "TRUE".....SSSSOOO, if it looks at F10 and it finds that it contains either of the values in F3:F5, it will add G10+H10. The very last part is if all conditions have been found to be false... i just made it return a zero (0).


Examples:
F10=50%:
=IF(ISNUMBER(F10)=TRUE,(G10+H10)*F10,IF(F10="RS",G10+H10,IF(F10=VLOOKUP(F10,$F$3:$G$5,1,0)=TRUE,G10+H10,0)))
=IF(TRUE,(G10+H10)*F10,IF(F10="RS",G10+H10,IF(F10=VLOOKUP(F10,$F$3:$G$5,1,0)=TRUE,G10+H10,0)))
=(2+3)*50%
=5*50%
=2.5 ===>1 will be displayed


F10=USD
=IF(ISNUMBER(F10)=TRUE,(G10+H10)*F10,IF(F10="RS",G10+H10,IF(F10=VLOOKUP(F10,$F$3:$G$5,1,0)=TRUE,G10+H10,0)))
=FALSE,(G10+H10)*F10,IF(F10="RS",G10+H10,IF(F10=VLOOKUP(F10,$F$3:$G$5,1,0)=TRUE,G10+H10,0)))
=FALSE,(G10+H10)*F10,FALSE,G10+H10,IF(F10=VLOOKUP(F10,$F$3:$G$5,1,0)=TRUE,G10+H10,0)))
=FALSE,(G10+H10*F10,FALSE,G10+H10,TRUE,G10+H10,0)))
=G10+H10
=2+3 =5 ===>1 will be displayed

I had both examples color coded by argument but for some reason it didnt save my 'edit'....