# 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!

Keith

• I think the issue is with your parenthesis placement in the formula. Take a look at the post here, and see if it helps: https://community.smartsheet.com/discussion/71871/nesting-index-match

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!

• 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.

• 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

Smartsheet CoE, Ignatiuz Software, Exton, PA