Fix an Excel VLOOKUP Formula by Adding the Column Index
Question details
Correct a VLOOKUP formula that is missing its return-column index.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Look up the value in J4 in the HCAPS worksheet and return the corresponding adjustment from column B.
- Observed behavior
- The VLOOKUP formula is missing the column index argument needed to identify the return column.
Confirm that the HCAPS worksheet contains lookup values in A3:A24 and adjustments in B3:B24. Check that J4 and the lookup values use the same data type.
Add the Return-Column Index to the Excel VLOOKUP Formula
Use this formula to find an exact match for J4 in HCAPS column A and return the adjustment from column B.
In Excel, click the cell containing the incomplete VLOOKUP formula and select the formula bar.
Replace the formula with =VLOOKUP(J4,HCAPS!$A$3:$B$24,2,FALSE) and press Enter.
Compare the returned value with the matching row on the HCAPS worksheet. If Excel displays #N/A, check that J4 matches a value in A3:A24 and that both use the same data type.

Correct the VLOOKUP Formula in WPS Spreadsheets
Open the workbook in WPS Spreadsheets and add the column index and exact-match argument to the VLOOKUP formula.
- 1. Open the workbook: In WPS Spreadsheets, choose Open and select the Excel workbook.
- 2. Select the formula cell: Click the cell with the incomplete VLOOKUP formula, then click the formula bar.
- 3. Enter the corrected formula: Type =VLOOKUP(J4,HCAPS!$A$3:$B$24,2,FALSE) and press Enter.
- 4. Save the workbook: Check the result against the HCAPS sheet, then choose File > Save to save your changes.

Frequently Asked Questions
What does the 2 mean in this VLOOKUP formula?
It identifies the second column of the lookup range A3:B24 as the return column. In this range, column B contains the adjustment.
Why does the formula use FALSE?
FALSE tells VLOOKUP to find an exact match for the value in J4 rather than an approximate match.
Why does VLOOKUP return #N/A?
An exact match may not exist in HCAPS!A3:A24, or J4 and the lookup values may use different data types. Check the values and their formats in both locations.
Why are there dollar signs in the lookup range?
The dollar signs in $A$3:$B$24 keep the lookup range fixed when you copy the formula to another cell.




