Search criteria in multiple sheets

Ron Goulet

I have created a Master Project sheet for three (3) different teams' projects (Team A, B, C). Common fields (columns) include "Project ID", "Project Manager", "Project Category" and "Target End Date".

I now need to search the Project ID in all three (3) teams' sheets, matching the ID entered in a fourth (4th) Master Operations sheet and return the PM, Category and End Date criteria @row from the team sheet containing the searched ID.

I believe I need "INDEX(MATCH..." formula, but can't figure it out beyond searching a single sheet. Would anyone happen to have a simple solution for searching multiple sheets and return data from those three (3) columns?

(NOTE: this could expand to include additional columns in the future like category, audience, geography...)

Thank You in advance for all help!! 😀

Ron Goulet

After receiving help from the Smartsheet Support Experts, we were able to get my formula request working.

Using 4-sheets total:

Sheet1: manually enter Project ID#

• Searches across 3-sheets to find Project ID and return Project Name

=IFERROR(INDEX({sheet2-Name}, MATCH(PID@row, {sheet2-ID}, 0)), IFERROR(INDEX({sheet3-Name}, MATCH(PID@row, {sheet3-ID}, 0)), IFERROR(INDEX({sheet4-Name}, MATCH(PID@row, {sheet4-ID}, 0)), "No Match")))

The "IFERROR" function tells the search to move from sheet2, to sheet3, to sheet4 looking for ID#

• if the ID# is not found, "No Match" will be returned
• When the ID# is found, the "INDEX" function will return the Project Name on the row it MATCHes the ID# to.
• the number of IFERROR(INDEX(...,MATCH(... functions, depends on the number of sheets being searched.

Easy when someone shows you how 😀

Thank You Smartsheet Support!

Heather Duff

Hi Ron,

Have you considered creating a report that pulls from all three project sheets? That would allow you to have all of the data contained in the three sheets in one place.

Hope this helps!

Best,

Heather

Ron Goulet

Hi Heather,

I very much appreciate your response Heather! A report was my original idea but I don't believe you can pull report data into sheet. I have tried usign VLOOKUP and could not access the report. I need to get the individual team project information into another sheet that an operations team uses to manage projects.

I.e. As Team A (or B...) creates a new project (or updates an existing project), that data populates the Master Operations sheet, informing/updating the Operations team of that project.

I am far from being a guru so if my thinking is wrong, I would love to learn :-)

Regards,

Ron

Ron Goulet

After receiving help from the Smartsheet Support Experts, we were able to get my formula request working.

Using 4-sheets total:

Sheet1: manually enter Project ID#

• Searches across 3-sheets to find Project ID and return Project Name

=IFERROR(INDEX({sheet2-Name}, MATCH(PID@row, {sheet2-ID}, 0)), IFERROR(INDEX({sheet3-Name}, MATCH(PID@row, {sheet3-ID}, 0)), IFERROR(INDEX({sheet4-Name}, MATCH(PID@row, {sheet4-ID}, 0)), "No Match")))

The "IFERROR" function tells the search to move from sheet2, to sheet3, to sheet4 looking for ID#

• if the ID# is not found, "No Match" will be returned
• When the ID# is found, the "INDEX" function will return the Project Name on the row it MATCHes the ID# to.
• the number of IFERROR(INDEX(...,MATCH(... functions, depends on the number of sheets being searched.

Easy when someone shows you how 😀

Thank You Smartsheet Support!

Felkernic

Hello Ron,

I know this an old post but I am really stuck on trying to get my status to update in my master sheet when I search three other sheets for the Project ID number that matches the Project ID number on the other three sheets. I am trying to understand how your formula returned the project name when it found the matching ID? Is part of your formula missing?

Thanks,

Nicole

Genevieve P.

Hey @Felkernic

The way an INDEX(MATCH works is that the first reference is the column with value you want to bring back (so in your case, it sounds like the Status column), then the second reference in the MATCH function is what you're looking to find in the other sheet (the Project ID).

I've bolded the references in the formula to show you where you'd add the column reference for what you want to return:

=IFERROR(INDEX({Sheet 1 - Status}, MATCH(PID@row, {Sheet 1-ID}, 0)), IFERROR(INDEX({Sheet 2 - Status}, MATCH(PID@row, {Sheet 2 -ID}, 0)), IFERROR(INDEX({Sheet 3 - Status}, MATCH(PID@row, {sheet 3 -ID}, 0)), "No Match")))

Here's more information on Index/Match:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

Cheers,

Genevieve

Ron Goulet

I couldn't have said it any better myself! Thanx Genevieve!

Felkernic

Thank you both so much! When I looked at Ron's formula it said sheet1-name after the INDEX and I typed in the name of Sheet1, not the column name and I could not get that to work. Thank you for clarifying that I enter the column name (ie Status). This worked out great! I have spent many hours trying to figure this out searching the help and learning section and the community boards and Ron your formula was the best I could find.

Thanks Again,

Nicole

Ron Goulet

Trust me, I would LOVE to take the praise but I have to give credit where it's due - it was all because of the expert people at Smartsheet who helped me out! Maybe I'll take a lil pat on the back for sharing. haha

Genevieve P.

Haha take a BIG pat on the back for sharing! That's what the Community is all about! 🤩