How to Use VLOOKUP in WPS Office Spreadsheets to Match Sheets and Return Column H
Question details
The user needs a formula to look up values in Sheet1 column A against Sheet2 column A and return the corresponding value from Sheet2 column H into Sheet1 column I.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a multi-sheet workbook and needs to populate Sheet1 column I with data from Sheet2 column H based on matching values in column A.
- Observed behavior
- The desired result is a formula in Sheet1 cell I2 that returns the matching column H value when the key exists and leaves the cell blank when there is no match.
Confirm that Sheet1 column A and Sheet2 column A contain the lookup keys in the same format, such as text, number, or no trailing spaces, and identify the data range in Sheet2 that includes columns A through H.
Use IFERROR with VLOOKUP to Return Sheet2 Column H into Sheet1 Column I
Use this method when you want to return a value from Sheet2 column H for every matching value in Sheet1 column A, with a blank result if no match exists.
This solution uses VLOOKUP with an absolute lookup range and wraps it in IFERROR so missing matches show as blank rather than an error.
Launch WPS Office Spreadsheets, open your workbook, click the Sheet1 tab, and select cell I2 where the returned value should appear.
Type =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$H$11,8,FALSE),"") into the formula bar and press Enter.
Use the fill handle at the lower-right corner of cell I2 to copy the formula down to the remaining rows in Sheet1 column I.
Return Matching Column H Values with VLOOKUP in WPS Office Spreadsheets
WPS Office Spreadsheets can directly complete this task. Enter the VLOOKUP formula in Sheet1 cell I2, wrap it in IFERROR, and fill it down to match values in Sheet2 column A and return the corresponding column H value.
- 1. Open WPS Office Spreadsheets: Launch WPS Office Spreadsheets, open the workbook that contains Sheet1 and Sheet2, and click the Sheet1 tab.
- 2. Select cell I2 and open the formula bar: Click cell I2 in Sheet1 and place the cursor in the formula bar above the worksheet grid.
- 3. Enter the VLOOKUP formula: Type =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$H$11,8,FALSE),"") and press Enter to return the matching value from Sheet2 column H.
- 4. Fill the formula down column I: Drag the fill handle from cell I2 down through the remaining rows in Sheet1 column I to apply the lookup to the rest of the list.
Frequently Asked Questions
What does the column index number 8 mean in this VLOOKUP formula?
The number 8 refers to the eighth column in the lookup range Sheet2!$A$2:$H$11. Counting from column A as 1, column H is the eighth column, so the formula returns the value from column H.
How do I make the formula return a blank instead of #N/A when there is no match?
Wrap the VLOOKUP formula in IFERROR, for example =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$H$11,8,FALSE),""). The second argument in IFERROR is an empty string, so unmatched rows display as blank.
Can I use this formula if my Sheet2 range has more than 11 rows?
Yes. Change $A$2:$H$11 to cover your actual data range, such as $A$2:$H$100 or $A$2:$H$1000. Make sure the range still starts in column A and includes column H.
Why do I need absolute references like $A$2:$H$11?
The dollar signs lock the lookup range so it does not shift when you copy the formula down column I. Without absolute references, the Sheet2 range would move on each row and could return incorrect results.
Does this VLOOKUP formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports Excel-compatible VLOOKUP and IFERROR formulas, so the same formula works in WPS Office and when the file is saved as an Excel-compatible workbook.
