Formula to replicate column values in Target sheet from source sheet

My source sheet has 3 columns such has Name, Location ID and Grade. When i provide the Location ID in the target sheet, i would require the Name and Grade values to be populated for that respective Location ID.

Source Sheet 1 has following values

In Source Sheet 2, will enter Location ID as ID 123

I need a result in Target Sheet as below. All 4 names as Location ID- ID 123. Please help me with formula.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!