Index Match or Index Collect ?
![Nikki B](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hi yall. I'm not good with the formulas but need them to streamline my employee tracking process. I have a, "Source Sheet" with Employee Name, Class, Dept that need to (match or collect) into another sheet, "Training Log". What formula would I use? I am hung up on the second part of MATCH after you reference the column@row. Thanks!!
Best Answer
-
there really isn’t a way to pull the name over. You could copy rows or do cell matching. How ever if you want it all automated that won’t work. You could put all the names you want to pull on the list manually and pull everything else. I don’t know how feasible that is with your data. The way an index works is it compares data on your current sheet to data on your reference sheet to pull the information. If name is your main identifier you have to enter it manually.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
=index({reference you want to pull},MATCH(column@row,{ref that matches the column@row}),0)
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thanks, Mark. What is the ref that matches the column? This is where I go blank…
=index({reference you want to pull},MATCH(column@row,{ref that matches the column@row}),0)
=index({SourceSheet}, MATCH (Employee Name@row, { can't figure this one out }),0)
-
In your source sheet. You should have all the employee names. the employee name your wanting should be on the same row as the information you are looking for.
=INDEX({SourceSheet}, MATCH([Employee Name]@row, {Employee Name Reference from source sheet}),0)
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
OK. I added the Employee Name reference from the Source Sheet as "DriverName". Error says "circuralreference"
=INDEX({SourceSheet}, MATCH([Employee Name]@row, {DriverName}), 0)
-
Ok That means your formula is circling back on itself. Is it the employee name you are trying to pull?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Yes. Employee Name, Class, Dept (3 columns)
SourceSheet: (Columns 3, 4, 5)
Training Log: (Columns 1, 2, 3)
-
ok you need some kind of identifier that’s in your source sheet that you are using to pull employee name over. What’s something I. Your training log that you manually enter. Or is pulled in by another formula that also matches something in your source sheet. That is not one of the three your trying to pull from your source sheet into your training g log
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
The name is the main identifier for me. Would another formula be better suited for this?
My goal is to have the Employee Name, Class, Dept populate on new sheets to track additional items (training and other items) not relevant to the Master Roster.
-
there really isn’t a way to pull the name over. You could copy rows or do cell matching. How ever if you want it all automated that won’t work. You could put all the names you want to pull on the list manually and pull everything else. I don’t know how feasible that is with your data. The way an index works is it compares data on your current sheet to data on your reference sheet to pull the information. If name is your main identifier you have to enter it manually.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thank you, Mark. I appreciate you taking the time!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!