The REF error you're seeing is because the range of cells your VLOOKUP was referring to have been deleted from the workbook.

If you're looking to use the "Location" rather than the "S.No" to look up values then you cannot use VLOOKUP as you have some values to the left of the reference value (Location).

What you can use is INDEX & MATCH.

Have a look at the attached. In row 13 I've added INDEX & MATCH formulas that do what I think you mean.

Let me know if I've misunderstood.