Excel/VLOOKUP

Advertisement


Question
Mr. Roberts,
I've searched high and low for someone to answer a question regarding VLOOKUP. You are the only person I have found that understands the VLOOKUP.

Question:

I have 5 employees, and I want to make a spreadsheet that remembers each employees hourly wage. So when I enter their name and hours worked it will multiply that by their hourly wage.I also want the hourly wage hidden. I'm not the smartest person but I really want to understand excel and the formulas. I thought about using a macro but don't know if that will work. Please help if you have time. I thank you for your time.

Answer
Tiffany

Here is your formula--

Assume that column A from cell A2 thru A6 contains your employree names.

Assume Cells B2 thru B6 contain the employees hours.


Assume that in cells I2 thru I6 you also have the emoloyee names and in cells J2 thru J6 you have the employee hourly rate.

Here is your formula in Cell C2.

=VLOOKUP(A2,I$2:J$6,2)*B2

 As you can see the formula looks up the value in A2 which is the first empoloyees name. It looks the name up in the range of cells I2 thru J6.  If it finds the employees name it returns the value next to his name in cell J2 this is the second column of the range.  It then multiplies the value returned from the vlookup formula by the value in cell B2, (the employees hours).

You can copy the formula down the rest of the cells C3 thru C6 and you will get the correct formula in those cells.

To work correctly it is best if you have the range you are looking in be sorted alphabetically by name, otherwise wrong values may be returned.

Try it out, it should work fine for you.

Richard
Florida
USA  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Roberts

Expertise

Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.

Experience

Have been working with Excel for about 20 years primarily in accounting and financial areas.

Education/Credentials
BA, Certified Public Accountant

©2016 About.com. All rights reserved.