INDEX(MATCH) returns #INVALID COLUMN VALUE

Nahum · 03/27/19

I have two sheets with a common "Name" column between them. I would like for a checkbox in the second sheet to reflect in the first sheet, factoring in that not all of the names will be used in the second sheet.

I would normally use INDEX(MATCH) for this purpose. MATCH() is able to return the row number for the Name where it appears in the other sheet (or an appropriate error for missing match), but INDEX(MATCH()) returns an error for the column containing checkboxes. I was expecting a 0 or 1 value depending on check status.

The INDEX reference is to the checkbox column on the other sheet. I have also tried VLOOKUP, but it returns the same #INVALID COLUMN VALUE error.

Does Smartsheet offer a better way to do this sort of lookup?

L_123 · 03/27/19

Can you post your formula?

Is the return column also a checkbox?

You can try to do an if statement with the return

=if(index(match()) = True,true, false

if that doesn't work the last recommendation I have to try would be a collect

=index(collect(Checkbox:Checkbox,Name:Name,Name@row),1)

Paul Newcome · 03/28/19

I generally end up using the first suggestion when building sheets with similar requirements to yours.

IF(the corresponding box on the other sheet = true, true)

=IF(INDEX(........., MATCH(........, ........., .....)) = 1, 1)

INDEX(MATCH) returns #INVALID COLUMN VALUE

Comments

Help Article Resources

Categories