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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!