Column Number Function

05/29/18 Edited 12/09/19

Hi is there a function that can return the column number when given the column name? This would help when columns are added in sheets that have vlookups referencing them.

Comments

  • Chris McKayChris McKay ✭✭✭✭✭

    Hi,

    Interesting question! I'd suggest you get used to using INDEX & MATCH instead of VLOOKUP as it is much more powerful and gives you greater control of your data. It also eliminates the silly need VLOOKUP has for you to define a column number to return. INDEX looks anywhere you tell it, including columns to the left of the data you are matching.

    Consider the VLOOKUP formula:

    =VLOOKUP(search_value, lookup_table,column_num, [match_type])

    against the corresponding INDEX/MATCH formula:

    =INDEX([range of return values], MATCH(search_value, lookup_table, [match_type]))

    They are similar, but INDEX just finds the value you're asking it to as you're essentially giving Smartsheet coordinates to find a value. This eliminates the fuss of specifics (that may be changed by users). The key differences are:

    1. The [range of return values can be anywhere on any sheet, of any size and even include multiple columns
    2. The data you're returning does not have to be to the right of your VLOOKUP search value and is not specified by it's column position. This means if someone changes a column name or position in the sheet you're referring to it doesn't matter, so we eliminate your original issue.
    3. INDEX/MATCH is inherently faster than VLOOKUP for large datasets as it is in essence doing less work (it's not including redundant columns to lookup and return values from only 2 columns)

    Unfortunately Smartsheet does not allow you to access the column names, so you're somewhat stuck if you want to find a column position using that criteria.

  • Awesome Chris - great advice!!  Could you give an example? 

    I've tried this: 

    =INDEX({DOC1' Range 13}, MATCH($[Column2]$2, {DOC1'}, false))

    but it's not working. 

    Not sure what you put after INDEX? :) 

    Now what if we add a line to the document - how can we make sure it includes it to? Can we do without having to select a "range"

  • Hi Chris

    We need this functionality. When doing a lookup or index match to another sheet, we need to dynamically reference a column.

    =VLOOKUP(search_value, MATCH({Column Name, lookup_table, [match_type]), [match_type])

    Is there any plan to provide this functionality?

  • Hi @yidnek

    There's a workaround for this.

    What you can do is have your first row on your sheet display all the columns names. within them. Then use a MATCH function looking for the column name and the range being only Row 1. This will return the number of the column you need for.

    And you can perfectly include this in your VLOOKUP formula as the column reference (and you can also include this as the column reference in an INDEX/MATCH lookup.

    Hope it helped!

Sign In or Register to comment.