Can you reference more than one sheet in an INDEX/MATCH formula?

amschock

I am trying to build a smartsheet with data from four other smartsheets, using formulas to populate several of the columns.

I would like to create one formula per so that it may be a column formula.

Ex: This formula populates an employee's first name from one other smartsheet.

=INDEX({23BP2-FIRSTNAME}, MATCH([Position Specific Code]@row, {23BP2-PSC}, 0))

This is the formula I typed to try to reference the same information from two other smartsheets:

=INDEX({23BP2-FIRSTNAME}, MATCH([Position Specific Code]@row, {23BP2-PSC}, INDEX({23CO1-FNAME}, MATCH([Position Specific Code]@row, {23CO1-PSC}, 0))))

Is there something I can add to the second formula to make this work?

Thank you!

Mike TV

@amschock

So it looks like you are trying to nest an INDEX formula within an INDEX formula. You can't do that.

What you can do though is create separate INDEX formulas and build them together, if they're working formulas on their own. Something like this could work:

=INDEX({23BP2-FIRSTNAME}, MATCH([Position Specific Code]@row, {23BP2-PSC}, 0)) + CHAR(10) + INDEX({23CO1-FNAME}, MATCH([Position Specific Code]@row, {23CO1-PSC}, 0))

What this will do is output the results of the first INDEX formula and drop down a line and output the results of the second INDEX formula. Just make sure to turn word wrap on for that cell or column.

If this isn't what you're after, please explain more what your intended results would be and maybe include sheet examples. You may need something like an INDEX COLLECT formula instead. Or another method may be to build out INDEX MATCH formulas within nested IF statements. It all depends on what your intended output results are and what's in those source cells you're referencing and INDEX MATCHing with.

amschock

@Mike TV thank you for your response.

Say we have 400 Position Specific Codes or PSC. We have four smartsheets that currently have ~100 of these codes (one per row of data), they are all unique codes.

I'm using the codes as the match to index first name for each unique code. There would actually need to be a total of 4 INDEX/MATCH formulas in one - but only one of the formulas would ever return a result because that code will only show up on one of the four smartsheets being referenced. Does that help?

Thank you!

Mike TV

@amschock

The solution you're looking for is IFERROR. Wrap your INDEX MATCHES up in an IFERROR formula. If your PSC isn't found in the first INDEX MATCH the cell would result with #NO MATCH, which technically is an error. So you build out like this:

=IFERROR(INDEX({23BP2-FIRSTNAME}, MATCH([Position Specific Code]@row, {23BP2-PSC}, 0)), IFERROR(INDEX({23CO1-FNAME}, MATCH([Position Specific Code]@row, {23CO1-PSC}, 0)), IFERROR(next index match, etc

amschock

Thank you, @Mike TV. I am still struggling as this did not work either. If you have other suggestions, I am willing to try them as I believe this should be possible.

Mike TV

@amschock

What error or result did you get when you tried the IFERROR solution?

amschock

@Mike TV I will go back and look so I can tell you but for right now I believe I have solved my issue by using Data Mesh. It seems to be for exactly what I need.

amschock

@Mike TV - here I am back on this issue. We only had data mesh for a trial period and I was determined to solve this without it.

Your solution did not work as is but put me on the right path to the most rewarding formula I've implemented to date. I just wanted to say thank you for your help.