How to Use VLOOKUP Formula to Return Hours Worked by Week
Question details
The user needs an Excel formula to look up a specific week number and return the corresponding hours worked from a designated table range using absolute references.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating weekly hours worked by pulling data from an existing lookup table.
- Observed behavior
- The user wants to populate cell I6 with the hours worked from the second column of range A17:G20, based on the week number entered in I5.
Ensure that your lookup table (A17:G20) is properly structured, with the week numbers listed in the first column (A) and the corresponding hours worked in the second column (B).
Apply the VLOOKUP Function with Absolute References
Use the standard VLOOKUP function combined with absolute references to lock the search parameters. This ensures the formula remains accurate even if you copy it to other cells.
The VLOOKUP function searches for a value in the first column of a table array and returns a value in the same row from another column. Adding absolute cell references (the $ symbol) keeps the lookup cell and table range fixed.
Click on cell I6, which is where you want the calculated hours worked to be displayed.
Type the formula: =VLOOKUP($I$5,$A$17:$G$20,2,TRUE) directly into the cell or the formula bar.
Press Enter to calculate the result. The spreadsheet will look up the week number located in cell I5, find it in column A, and return the corresponding hours worked from column B.
Calculate Weekly Hours Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like VLOOKUP, making it incredibly simple to retrieve matching data, track work hours, and manage large datasets efficiently.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your weekly hours table.
- 2. Insert the VLOOKUP function: Select cell I6, navigate to the Formulas tab, and click Insert Function. Search for VLOOKUP and select it.
- 3. Input the function parameters: In the dialog box, set Lookup_value to $I$5, Table_array to $A$17:$G$20, Col_index_num to 2, and Range_lookup to TRUE or FALSE. Click OK to finish.
Frequently Asked Questions
Why do I need to use absolute references ($) in my VLOOKUP formula?
Absolute references (like $A$17:$G$20) lock the specific cells or ranges in place. If you drag or copy the formula to another cell, the lookup value and the table array boundaries will not shift, preventing calculation errors.
Should I use TRUE or FALSE at the end of my VLOOKUP formula?
Use FALSE when you want an exact match (highly recommended for looking up specific identifiers like week numbers). TRUE is used for an approximate match and requires the first column of your lookup table to be sorted in ascending order.
Why does my VLOOKUP formula return an #N/A error?
The #N/A error occurs if the lookup value (the week number in I5) cannot be found in the first column of your table array (A17:A20). Ensure the week number exists in the first column and check for accidental leading or trailing spaces.
