SOLVED

Value not available error for Index match formula..

%3CLINGO-SUB%20id%3D%22lingo-sub-2138165%22%20slang%3D%22en-US%22%3EValue%20not%20available%20error%20for%20Index%20match%20formula..%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2138165%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EPlease%20help%20me%20for%20the%20index%20match%20formula%20use.Please%20check%20the%20sheet%202...some%20value%20is%20showing%20value%20not%20available%20error%20with%20a%20same%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2138165%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2138291%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20not%20available%20error%20for%20Index%20match%20formula..%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2138291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F967783%22%20target%3D%22_blank%22%3E%40Hiren1993%3C%2FA%3E%26nbsp%3BYour%20sheet%20contains%20rounding%20differences.%20for%20instance%2C%20in%20F29%20you%20are%20trying%20to%20match%200.91%20with%26nbsp%3B0.910000000000001%20in%20B72.%20It%20doesn't.%20Hence%2C%20the%20NA%23%20error.%3C%2FP%3E%3CP%3EClean-up%20column%20B%20(i.e.%20get%20rid%20off%20the%20extra%20decimals).%20In%20column%20G%20you%20also%20need%20to%20write%20the%20formula%20to%20become%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DROUND(SALE!E2%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20ensures%20that%20Excel%20picks-up%20two%20decimals%20from%20the%20sales%20sheet%20as%20well.%3C%2FP%3E%3CP%3EA%20cleaned%20file%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

Please help me for the index match formula use.Please check the sheet 2...some value is showing value not available error with a same formula.

 

Please help...

2 Replies
best response confirmed by Hiren1993 (New Contributor)
Solution

@Hiren1993 Your sheet contains rounding differences. for instance, in F29 you are trying to match 0.91 with 0.910000000000001 in B72. It doesn't. Hence, the NA# error.

Clean-up column B (i.e. get rid off the extra decimals). In column G you also need to write the formula to become like this:

=ROUND(SALE!E2,2)

This ensures that Excel picks-up two decimals from the sales sheet as well.

A cleaned file is attached.