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

Keith E

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

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

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

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

https://www.linkedin.com/in/amitinddr/

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,

Keith