Formula Error

Hello,

I was hoping to get some help with a formula. We are using INDEX/MATCH. Its all working great when we are referencing one column. When we change the reference to select a range of columns, the formula doesn't work and we get #UNPERISHABLE. Can someone help us figure out how to reference a range of columns?

Here is our formula as we reference column 3 only:

=INDEX({Subcategories Range}, MATCH(Description2, {Project Title}, 0), 3)


How can reference columns 1-4 and not a single column?

We tried 1:4, we tried adding a OR function.

=INDEX({Subcategories Range}, MATCH(Description2, {Project Title}, 0),1:4 )

Help Please :-)

Tags:

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Hey @Cat.Cosby

    INDEX/MATCH is specific to just one column. You will need to create a new Reference for each column if you want to use INDEX/MATCH. Another option is VLOOKUP which can reference multiple columns but if someone adds in or deletes a column the reference number doesn't update and the wrong data can be displayed. Personally, I would choose INDEX/MATCH for just about everything for this reason unless there are tons of columns to reference.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!