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.
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:
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!
I am wanting to do something similar with INDEX MATCH. I need to use MATCH to return the row number and the column number.
I can get it to return the row number, but I'm having trouble with the second MATCH to return the column number.
Since Smartsheet doesn't have the ability to reference column headers, I've duplicated the headers on ROW 1 of the source/referenced sheet.
=INDEX({Addresses}, MATCH([Mailing Instructions]@row, {MailingInstructions}, 0), MATCH("Address Line 1", {FedEx Address List Range 2}, 0))
Unfortunately, I'm getting the #INVALID VALUE error.
I know when I use this formula in Excel, I have to press CTRL+SHIFT+ENTER. Otherwise I get the #VALUE! error in Excel. Is there an equivalent step in Smartsheet to get this formula to work?
@Tori Isbell To get your formula to work
"Address Line 1" would need to be in Row 1, and {FedEx Address List Range 2} would have to be referencing Row 1.
You would also need to ensure that the {Addresses} range covers all of the columns.
This particular error is telling us that either the MATCH for the row number or the MATCH for the column number is returning a number that is either higher than the number of rows in the {Addresses} Range or higher than the number of columns within the {Addresses} range (or both).
To test this, use each of the MATCH functions as standalone formulas to see what numbers they return. You can then double check to verify that the {Addresses} range covers everything.
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…