Formula to replicate column values in Target sheet from source sheet

Nishu

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.

Paul Newcome

Is there a reason you are wanting to pull this via formula as opposed to a report?