Jan 15 2020 11:29 PM
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!
Jan 15 2020 11:37 PM
Jan 15 2020 11:44 PM
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.
Jan 15 2020 11:56 PM