INDEX/COLLECT with Partial Text Match


Hi -

I'm trying to use an Index/Collect formula to bring back information based upon two text variables that could be included in my search range. Below is the formula as I have written it and I get and Invalid Column Value error when it runs. The columns that I am pulling information from are restricted dropdown columns that contain text and the column my formula lives in is a Text column. I've tried changing column types but it does not effect the outcome. Am I using this formula incorrectly? Thanks for any help!

=INDEX(COLLECT([DAY 1]1:[DAY 8]1, [DAY 1]@row:[DAY 8]@row, "TVL", [DAY 1]@row:[DAY 8]@row, "TVL/X"), 0)



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!