SOLVED

VLookup that combines IF/ISNA statements?

%3CLINGO-SUB%20id%3D%22lingo-sub-2625568%22%20slang%3D%22en-US%22%3EVLookup%20that%20combines%20IF%2FISNA%20statements%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2625568%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20do%20VLookup%20with%20an%20IF%20and%20ISNA%20statement%20combined%20so%20that%20the%20VLookup%20searches%20the%20table%20and%20multiplies%20I16%20by%20the%20appropriate%20penalty%20in%20the%20penalty%20schedule%20box.%20But%20if%20I16%20is%20negative%20(like%20in%20this%20case)%2C%20instead%20of%20saying%20%23N%2FA%2C%20it%20would%20be%20a%20dash%20for%20zero.%20I've%20been%20able%20to%20create%20separate%20formulas%20but%20cannot%20figure%20out%20how%20to%20join%20them%20together.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2625568%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2625608%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20that%20combines%20IF%2FISNA%20statements%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2625608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120891%22%20target%3D%22_blank%22%3E%40KScott85%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(I16%20%26lt%3B%200%2C%200%2C%20I16*VLOOKUP(...)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm trying to do VLookup with an IF and ISNA statement combined so that the VLookup searches the table and multiplies I16 by the appropriate penalty in the penalty schedule box. But if I16 is negative (like in this case), instead of saying #N/A, it would be a dash for zero. I've been able to create separate formulas but cannot figure out how to join them together.

1 Reply
best response confirmed by KScott85 (Occasional Contributor)
Solution

@KScott85 

Perhaps like

=IF(I16 < 0, 0, I16*VLOOKUP(...) )