My formula does not work when I need to reference more than two sheets

I am trying to run a duplicate check with 10 other Smartsheets, but my formulas keep breaking after I reference more than two sheets.

This is the formula I am using that identifies when the duplicate is matched with Index/Match. It works for the two sheets (Sheet 1 & 2), but when I add Sheet 3, it breaks:

=INDEX({Sheet 1 Range 1}, MATCH([Range 2]@row, {Sheet 1 Range 2}, 0), INDEX({Sheet 2, Range 1}, MATCH([Range 2]@row, {Sheet 2, Range 2}, 0)))

If this cannot work, I am fine with a simple vlookup, but I can't seem to get that to work for more than one sheet. This is the formula I am using:

=VLOOKUP([Range 2]@row, {Sheet 1 Range 2}, 1, 0)

Any help would be greatly appreciated.

Thank you!



  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    I think the issue is with your parenthesis placement in the formula. Take a look at the post here, and see if it helps:

    If I'm following the logic, it looks like you need to close your parenthesis after each INDEX/MATCH combo, rather than stacking them at the end (as you would with nested IF formulas)

    Hope this helps!

  • Keith E
    Keith E ✭✭

    Hello Danielle, and thank you for your answer. When I close the parenthesis (using two instead of one at the end of the combo), I am still getting an #unparseable error when I reference the third sheet or third combo of Index/match.

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭

    Hi @Keith E

    Did you try using IF condition. To check in more than 1 sheet, you can “IF” condition. If the formula does not find the results in sheet 1, it will look into sheet 2. This is how the formula would look like.

    =IF(INDEX({Sheet 1 Range 1}, MATCH(Name@row, {name}, 0)) <> "", INDEX({Sheet 1 Range 1}, MATCH(Name@row, {name}, 0)))

    So it is checking in sheet 1 if value is not blank then get the data from sheet 1 same way in the else condition you can check in sheet 2 with same condition.

    I hope this helps!


    Best Regards

    Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

  • Keith E
    Keith E ✭✭

    Hello @Amit Wadhwani , I couldn't get the IF addition to the formula to work. Could you please give me an example of what it would look like for three sheets, if we had Sheet 1, Sheet 2, Sheet 3, and using the different ranges and where I would add the additional sheets?

    Thank you,


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!