Vlookup and data validation in same clumns

LeeMan88

New Member
Joined
Dec 13, 2013
Messages
28
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top