Index/Match help
Hello SS Community. After struggling with syntax for VLOOKUP cross-sheet formula, I found information on index/match. I think this may be the formula and solution that I'm looking for.
I have sheets for each term with student data and course credit counts for that term. Here are two examples:
Not all students are in each term sheet. I want to create a new sheet that combines credit counts for all students enrolled in the program by inserting a cross-sheet formula with an index/match (or vlookup if that's the best solution). I am looking for syntax for the "Transfer" and "Fall" columns below that returns the red value.
In the Credit Count sheet in the cell with the red 3, I tried =INDEX({OL Transfer Credits Range 1}, MATCH(ID@row, {OL Transfer Credits Range 2})) where Range 1 is the column "Transfer Credits" and Range 2 is the column "ID" in the OL Transfer Credits Sheet.
I get the correct value, but when I try to apply the same formula to the rest of the cells in the "Transfer" Column, I do not get the correct value that matches the student record from the OL Transfer Credit Sheet.
Thanks for any insights!
Answers
-
Try taking advantage of the 3rd portion of the MATCH function that allows you to specify an exact match.
=INDEX({OL Transfer Credits Range 1}, MATCH(ID@row, {OL Transfer Credits Range 2}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!