Getting values from entered names


I'm trying to rememeber if it is possible to create a formula that would enter the Rate of Pay when a surname is enter into a field. 

Is it possible when I add name under the day worked for the rate of pay to be associated with the name so that it imports the rate of pay instead of me manually doing it for each day?



It's possible in a number of ways but you'll need the employees surnames and rates of pay in a data table. You can then use Excel worksheet functions like VLOOKUP or INDEX/MATCH to provide that data but you might need to include their first name too. 
Please edit your question above to give more information, preferably providing a test file so we can look at it and give specific help.
Don't provide real employee data though!
John_Ru (rep: 502) Jan 11, '21 at 5:45 pm
Add to Discussion


Selected Answer


Thanks for attaching the file. Please see my modified version attached...

In that, I've used the VLOOKUP function instead of typing the cell containing the surname

E.g. in (yellow) cell C15, you had =SUM(C15*C4). Note that you didn't actually need the SUM function there=C15*C4 does the same thing.

I've replaced it with this: 

I've copied that to C16, C22 and C23 but it can be copied anywhere where there is a cell to the left containing a name in the range of cells in bold above.

The VLOOKUP looks for what in B15 in the left-most column (1) of the range $B$3:$C$5 (in bold above). The FALSE bit requires an exact match (so not the surname close to it) and if it finds one, it returns the value in the column indicated by the 2 in the formula i.e. the next column, C. That then get mulitpled with the hours.

Hope this is what you want. Look in the Tutorial section and you'll find more guidance on this function (it has its quirks!)

REVISION 1: You can make life even easier for yourself by using data validation. I've attached a revised sheet where you don't need to type the overtime names (and risk an error for the VLOOKUP). Click in B27 and a down arrow will appear where you can just pick one of the names from the table without typing (I've cheated and added the row below the table so there's a blank option). The "real" ones B15, B16 etc, are the same and you can copy a cell to anywhere in the sheet and the validation will follow: values you can enter can only match that list. Here's a tutorial to help you understand data validation: Excel Data Validation - Limit What a User Can Enter into a Cell.

Note that you can apply this to the answer from Variatus too (also check out the tutorials about automatically updated validation).



Thanks, that works perfectly!
sbrading (rep: 2) Jan 11, '21 at 7:07 pm
Thanks but please see Revision 1 to my Answer (at the end), it will save you time with either Answer. 
John_Ru (rep: 502) Jan 12, '21 at 4:36 am
Add to Discussion

The attached workbook is an upgraded copy of the one you posted. In it I changed your OT_ROP table into a real table. That's easy to do. Just select the entire table, select Insert > Table from the Ribbon and confirm that your table has headers in the dialog box that opens. After doing this, the table's default name will be displayed at the top left of the screen. Change it to OT_ROP.

The advantage of doing this is that you can have this table anywhere in your workbook and it will expand automatically as you add more names. Mind, however, that for the use of VLOOKUP in the following the employee's name must be in the first column. (If this becomes inconvient over time you can probably se XLOOKUP later if you have a new version of Excel.)

The lookup function is in E15. From there you can copy it down to E16 and E22:E23.


As you see, the formula looks up the name in B15 in the OT_ROP range above created and returns the value from the second column of that range. 

What if the name in B15 isn't found? I first thought of adding an error handler in the formula but then decided against that. In fact, I think, you will want to be alerted if there is an error - as might be caused by mis-spelling of the name in B15.



Thank you, that makes it even easier!
sbrading (rep: 2) Jan 11, '21 at 7:34 pm
@sbrading- To make it even easier, I've amended my answer to show the user how to use data validation to save typing the names when overtime is done.It will work for the Answer too- please see revision 1 to my Answer

@ Variatus- good answer and an improvement on mine (it was nearly my bedtime when I answered!).
John_Ru (rep: 502) Jan 12, '21 at 4:37 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login