I have searched and tried various solutions elsewhere but none seems to work for both conditions. I would like to have the formula:
=IF($A2="Elementary", VLOOKUP($B2,'ELEM PRINCIPAL'!$B:$AA,7,false), VLOOKUP($B2,'HS PRINCIPAL'!$B:$AA,7,false))
inserted as new rows are added to my spreadsheet. That is all simple I hope.