• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX - Returning defined column name instead of column number

J

JCTalk

Member
Hi Guys,

I'm using index and match instead of VLOOKUP, and whilst it's working perfectly, I am constantly adding new columns into my workbook. The INDEX return column numbers don't change automatically when I add a new column in so it starts returning the wrong information.

I was wondering if there is a way to use defined column name in an INDEX/MATCH formula instead of a column number?

Thanks guys.
 
Hi many thanks for your reply.

There isn't really a dummy file to post. It's more of a generic question to know if it's possible to use defined names instead of column numbers.

Assume my current lookup is...
INDEX(A1:F10,MATCH("Chandoo",A1:A10,0),4)

What I'm looking to do is...
INDEX(TableRange,MATCH("Chandoo",Websites,0),CoolFactor)

TableRange and Websites ranges are easy to define and use, but "CoolFactor" would be the replacement for column number 4 in this instance.

Because the range is named, I'm hoping if I add a new column into the workbook, the CoolFactor range will sort itself out.

Does that make sense? Try to ignore the scenario. Its more of a generic question as to whether its possible to use a named column, rather than a column number to be returned.

Many thanks
 
Hi.

Defining CoolFactor as a static value, e.g. 4, will not result in changes to this value, no matter what actions you perform within the worksheet.

Defining it as, e.g.:

=COLUMNS($A:$D)

will, currently, be equivalent to a value of 4.

Moreover, if any columns are inserted between column A and column D, this definition will amend accordingly. For example, inserting a single new column within that range will mean that the above becomes:

=COLUMNS($A:$E)

and so your INDEX/MATCH formula will continue to reference the correct column.

Regards
 
Hi XOR LX,

Many thanks for your suggestion.

I see. So the thought there is that assuming a column is added within the range it will change the defined range automatically. If a column is added outside the range its irrelevant because it doesn't change the column number to be returned.

I like it. It serves as a good workaround.

Many thanks XOR LX.
 
You must log in or register to reply here.
Back
Top