'%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 in Excel to Match Data Across Worksheets
Question details
The user needs a formula to match specific lookup values and return corresponding associated values from a different worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up and matching specific data points across multiple worksheets within the same workbook.
- Observed behavior
- A VLOOKUP formula needs to be constructed to pull the correct varying results from another sheet based on an exact match.
Ensure that the source worksheet containing your data and the destination worksheet where you want the results are located in the same workbook.
Use Exact-Match VLOOKUP Formula
Apply the VLOOKUP function to search for a specific value in one sheet and return a corresponding value from another sheet.
VLOOKUP is ideal for vertical data lookups. It requires the lookup value, the range of data on the second sheet, the column number of the desired result, and a requirement for an exact match.
Click on the cell in your current worksheet where you want the matched value to be displayed.
Type =VLOOKUP(A1,Sheet2!A:B,2,FALSE) into the formula bar. Replace A1 with the cell reference containing your lookup value.
Replace Sheet2!A:B with the actual name and column range of your target worksheet where the data is stored.
Ensure the column index number (e.g., 2) corresponds to the column containing your desired result on the target sheet.
Press Enter to apply the formula. You can then drag the fill handle down to apply this exact-match lookup to other rows.
Use HLOOKUP for Horizontal Data
If your source data is arranged in rows instead of columns, use the HLOOKUP function.
Match Data Across Sheets Easily with WPS Spreadsheet
WPS Spreadsheet fully supports VLOOKUP, HLOOKUP, and advanced lookup functions. You can seamlessly manage and analyze data across multiple worksheets using an intuitive interface and built-in formula builder.
- 1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your multiple sheets.
- 2. Insert the VLOOKUP function: Click the Formulas tab, select Insert Function, and search for VLOOKUP.
- 3. Configure the formula parameters: Follow the interactive dialog to select your lookup value, define the table array from the second sheet, and set the column index number.
Frequently Asked Questions
Why is my VLOOKUP returning an #N/A error?
This usually means the lookup value does not exist in the first column of your target range, or there are trailing spaces in your data that prevent an exact match.
Can I use VLOOKUP to search data in a completely different workbook?
Yes, you can reference another workbook by including the file name and path in brackets, such as =VLOOKUP(A1,'[Data.xlsx]Sheet1'!A:B,2,FALSE), provided the source file is open or the path is fully specified.
What does the 'FALSE' argument in the VLOOKUP formula do?
The 'FALSE' argument ensures the formula only returns an exact match. If an exact match is not found, it returns an error instead of an approximate match.
