Apply Data Validation to result of Vlookup?

Hi folks, im not sure if this is possible and if it is, i need a Macro / VBA free solution as my network has disabled these functions .

I currently have a spreadsheet that looks up a person's details based on their assigned number. i.e. cell A1 (manually enter assigned number), cell B1 (Vlookup person's Name) and C1 (Vlookup person's Job Title).

What i need is for the data returned to C1, for example "Manager" to be validated against a list of possible options (e.g Sales Assistant, Assistant Manager, Cleaner) and for an error message to pop up if the data returned is not in the list. So in this example the, the Vlookup returning "Manager" should bring up the error. However, when i set this up, the error only comes up when i double click the cell (C1) and hit enter (to calculate the formula?). If i dont do this, the incorrect value is allowed by the validation when it shouldnt be...

Any thoughts on how to have the Vlookup data checked automatically against the validation criteria whenever the assigned number in cell A1 is changed?

Please Login or Register  to view this content.

Valid positions range could be a list in another tab