My calculation shows #N/A using IF and VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-2594122%22%20slang%3D%22en-US%22%3EMy%20calculation%20shows%20%23N%2FA%20using%20IF%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594122%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20find%20difficulty%20to%20fix%20an%20error%20in%20my%20calculation.%3C%2FP%3E%3CP%3EPlease%20find%20the%20attachment%20below.%3C%2FP%3E%3CP%3EYou%20can%20find%20the%20error%20at%20sheet%20asumsi%20cell%20D20%20untill%20D23.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIll%20be%20more%20than%20happy%20if%20we%20can%20discuss%20further%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethankyou%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2594122%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594137%22%20slang%3D%22en-US%22%3ERe%3A%20My%20calculation%20shows%20%23N%2FA%20using%20IF%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1114735%22%20target%3D%22_blank%22%3E%40AnggiPutri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20should%20use%20FALSE%20as%20fourth%20argument%20of%20VLOOKUP.%3C%2FP%3E%0A%3CP%3EFor%20example%20in%20D20%3A%3C%2FP%3E%0A%3CP%3E%3DIF(%24E%2420%3D802%2CVLOOKUP(B20%2C%24P%248%3A%24S%2411%2C3%2C%3CSTRONG%3EFALSE%3C%2FSTRONG%3E)%2CIF(%24E%2420%3D1000%2CVLOOKUP(B20%2C%24P%248%3A%24S%2411%2C4%2C%3CSTRONG%3EFALSE%3C%2FSTRONG%3E)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20workbook%20has%20several%20circular%20references%2C%20for%20example%20in%20I20%20(change%20it%20to%20100%25)%2C%20and%20in%20J67%3AJ76%20(not%20sure%20what%20that%20should%20be)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello everyone,

 

I find difficulty to fix an error in my calculation.

Please find the attachment below.

You can find the error at sheet asumsi cell D20 untill D23.

 

Ill be more than happy if we can discuss further

 

thankyou

1 Reply

@AnggiPutri 

You should use FALSE as fourth argument of VLOOKUP.

For example in D20:

=IF($E$20=802,VLOOKUP(B20,$P$8:$S$11,3,FALSE),IF($E$20=1000,VLOOKUP(B20,$P$8:$S$11,4,FALSE)))

 

Your workbook has several circular references, for example in I20 (change it to 100%), and in J67:J76 (not sure what that should be)