'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use VLOOKUP to Return a Cell Value from Another Sheet
Question details
The user needs to retrieve a specific cell value from a different worksheet using a lookup formula based on a matching identifier.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing and retrieving corresponding data from a master dataset located on a separate worksheet.
- Observed behavior
- The user requires the correct syntax to reference another sheet within a VLOOKUP function and needs to handle potential #REF! or #N/A errors when a match is not found.
Ensure both worksheets are contained within the same workbook and clearly identify the exact column index containing the result you want to retrieve. The lookup value must be located in the first (leftmost) column of your selected search range on the target sheet.
Use VLOOKUP with IFNA to Pull Data Across Sheets
This method uses the standard VLOOKUP function combined with IFNA to return the correct value from another sheet while keeping the cell blank if no match is found.
When referencing another sheet in your formula, you must include the sheet name followed by an exclamation mark (e.g., Sheet1!). Combining VLOOKUP with IFNA is highly recommended as it prevents unsightly #N/A error codes when a data point is missing in the source sheet.
Click the cell on your current worksheet (e.g., Sheet2) where you want the returned value to appear.
Type the formula =IFNA(VLOOKUP(A1,Sheet1!$A$1:$D$4,3,FALSE),""), replacing 'A1' with your specific lookup cell.
Ensure Sheet1!$A$1:$D$4 matches your actual data range on the other sheet. Using absolute references (adding $ symbols) prevents the search range from shifting when you copy the formula down.
Change the column index number (in this example, '3') to match the specific column in your table array that contains the result you want to return.
Press Enter to execute the function, then drag the fill handle down to apply the formula to the remaining rows.
Troubleshoot and Fix the #REF! Error
Resolve the common #REF! error that occurs when a VLOOKUP formula attempts to extract data from a column index that falls outside the defined table array.
Easily Manage Cross-Sheet Formulas with WPS Spreadsheet
WPS Spreadsheet provides an intuitive formula editor with autocomplete suggestions, making it incredibly simple to build complex cross-sheet VLOOKUP and XLOOKUP formulas without encountering syntax errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple worksheets.
- 2. Access the Function menu: Click the 'Formulas' tab on the top ribbon and select 'Insert Function' to easily search for VLOOKUP.
- 3. Select ranges intuitively: Use the visual function arguments dialog box to click through your different sheets and highlight your lookup ranges without having to type the syntax manually.
- 4. Generate the formula: Click 'OK' to instantly generate and insert the precise cross-sheet lookup formula.
Frequently Asked Questions
Why does my cross-sheet VLOOKUP return a #N/A error?
A #N/A error means the exact lookup value cannot be found in the first column of your search range. Ensure there are no hidden trailing spaces, formatting mismatches (such as text vs. numbers), or typos in your lookup data.
How do I reference a sheet name that contains spaces in my formula?
If your worksheet name contains spaces, you must wrap the sheet name in single quotation marks within the formula. For example, use 'Data Master'!$A$1:$D$4 instead of Data Master!$A$1:$D$4.
Can I use XLOOKUP instead of VLOOKUP to pull data between sheets?
Yes. XLOOKUP is an excellent alternative because it does not require the lookup value to be in the first column of the dataset. A standard XLOOKUP syntax between sheets looks like =XLOOKUP(A1, Sheet1!A:A, Sheet1!C:C, "", 0) to search column A and retrieve matching data from column C.
How do I return a blank cell instead of a 0 when the matching cell is empty?
If VLOOKUP finds a match but the result cell is empty, it returns a 0. To fix this, you can append an empty string to the end of your formula (e.g., =VLOOKUP(A1,Sheet1!A:C,3,FALSE)&"") or use an IF statement to check for blanks.
