Vlookup and data validation in same clumns

[LeeMan88]

Hello

I'm working on enhancing my xls where currently, I use a Vlookup to show SurName (Col J), FirstName (Col K), Rate (Col M) and Hours (Col N) based on knowing a unique ID.

So formula used in Col A is =IFERROR(VLOOKUP($L1,Personnel,2,FALSE),"") where L1= a unique ID, Personnel = table range name containing columns for Surname, FirstName and cost, 2=Surname (of the Unique salary ID). The value returned is the Surname for the unique Sal ID.
So user enters the salary ID of a resource (Col J) and the formula returns the Surname. Similar formula is used in next column for FirstName (Col K), Rate (Col M) and Hours (Col N) where the only difference is the column used in VLOOKUP.

Now to the trickier bit that I was hoping to get some help on.
The above approach works well IF a Unique ID is known BUT when this is not the case, I wanted to provide user the option of entering a value from a validation list for a generic role name. This list contains rate and Hour information that I would also need to change in my vlookup ie If Unique salary ID, use resource rate, if generic role, use rate / hours for the generic resource type. A validation list offers a drop down pick list that a user can select the generic role type.

Anu ideas would be great and again hoping that forum saves me yet again.

 

[konew1]

I think you need build two separate VlOOKUP formula for each data field (Name, rate, etc)
Have two input cells, say A1 if you know the unique ID and A2 if you want the generic data.
Then your result formula would be in the style
=IF(AND(A1< >"",A2< >""),"ERROR, only look for one type at a time", if(A1< >"","existing formula","generic lookup formula"))