How to get over the Table Name "Index" when using Index-Match formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1111978%22%20slang%3D%22en-US%22%3EHow%20to%20get%20over%20the%20Table%20Name%20%22Index%22%20when%20using%20Index-Match%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111978%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EHello!%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI'm%20having%20some%20troubles%20as%20i%20get%20a%20%23REF!%20error%20because%20someone%20else%20at%20some%20point%20created%20a%20table%20with%20the%20name%20%22Index%22%2C%20and%20when%20i%20evaluate%20the%20formula%2C%20excel%20retrieves%20the%20table%20range%20instead%20of%20identifying%20it%20as%20a%20usage%20of%20formula.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EIs%20there%20anyway%20to%20overcome%20this%20without%20having%20to%20change%20the%20name%20of%20the%20table%20(as%20I%20do%20not%20know%20where%20they%20are%20using%20it%2C%20nor%20who%20is%20using%20it.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ERight%20now%20i've%20just%20used%20vlookup%20for%20what%20I%20need%20to%20do%2C%20but%20wondering%20if%20there's%20any%20way%20to%20continue%20using%20index-match%2C%20as%20i%20much%20prefer%20that%20one%20over%20vlookup.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThanks!%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EEdit%3A%20As%20an%20example%2C%20my%20formula%20would%20be%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3DIndex(E468%3AE483%2CMATCH(A483%2CF468%3AF483%2C0))%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EBut%20Index%20is%20a%20table%20name%20for%20range%20%24A%242%3A%24J%241815%2C%20so%20when%20i%20evaluate%20step%20by%20step%2C%20excel%20evaluates%20it%20as%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3D%3CU%3EIndex%3C%2FU%3E(E468%3AE483%2CMATCH(A483%2CF468%3AF483%2C0))%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3D%3CEM%3EIndex!%24A%242%3A%24J%241815%3C%2FEM%3E(E468%3AE483%2CMATCH(%3CU%3EA483%3C%2FU%3E%2CF468%3AF483%2C0)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3Eand%20so%20on%2C%20then%20ends%20at%20%3D%23REF!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1111978%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-1111987%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20over%20the%20Table%20Name%20%22Index%22%20when%20using%20Index-Match%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111987%22%20slang%3D%22en-US%22%3EChanging%20the%20table%20name%20should%20not%20lead%20to%20any%20issues%20as%20Excel%20automatically%20changes%20all%20the%20references%20to%20the%20new%20name.%20So%20I%20guess%20this%20would%20be%20your%20best%20shot%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111998%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20over%20the%20Table%20Name%20%22Index%22%20when%20using%20Index-Match%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111998%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYeah%2C%20i've%20thought%20of%20it%2C%20but%20i'm%20just%20afraid%20whoever%20is%20using%20the%20name%20would%20suddenly%20find%20their%20formulas%20failing%20in%20the%20future.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENamed%20ranges%20that%20are%20named%20%22Index%22%20would%20still%20allow%20index-match%20to%20work%20though%2C%20but%20because%20the%20one%20in%20my%20workbook%20is%20a%20table%20name%2C%20so%20oddly%20it%20doesnt%20identify%20it%20as%20a%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1112022%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20over%20the%20Table%20Name%20%22Index%22%20when%20using%20Index-Match%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1112022%22%20slang%3D%22en-US%22%3EIf%20I%20was%20you%20I%20would%20save%20a%20copy%20of%20the%20file%2C%20change%20the%20name%20of%20the%20table%20and%20then%20compare%20the%202%20files.%20If%20they%20show%20the%20same%2C%20it%20worked...%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello!

 

I'm having some troubles as i get a #REF! error because someone else at some point created a table with the name "Index", and when i evaluate the formula, excel retrieves the table range instead of identifying it as a usage of formula.

 

Is there anyway to overcome this without having to change the name of the table (as I do not know where they are using it, nor who is using it.

 

Right now i've just used vlookup for what I need to do, but wondering if there's any way to continue using index-match, as i much prefer that one over vlookup.

 

Thanks!

 

Edit: As an example, my formula would be:

=Index(E468:E483,MATCH(A483,F468:F483,0))

But Index is a table name for range $A$2:$J$1815, so when i evaluate step by step, excel evaluates it as:

=Index(E468:E483,MATCH(A483,F468:F483,0))

=Index!$A$2:$J$1815(E468:E483,MATCH(A483,F468:F483,0)

and so on, then ends at =#REF!

3 Replies
Highlighted
Changing the table name should not lead to any issues as Excel automatically changes all the references to the new name. So I guess this would be your best shot
Highlighted

@PascalKTeam 

 

Yeah, i've thought of it, but i'm just afraid whoever is using the name would suddenly find their formulas failing in the future. 

 

Named ranges that are named "Index" would still allow index-match to work though, but because the one in my workbook is a table name, so oddly it doesnt identify it as a formula.

Highlighted
If I was you I would save a copy of the file, change the name of the table and then compare the 2 files. If they show the same, it worked...