Fix an Excel VLOOKUP Circular Reference and Worksheet Reference Error
Question details
The user needs to correct a VLOOKUP formula that creates a circular reference and uses an incomplete worksheet reference.
- Product
- Microsoft Excel or WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet should return a VAT rate based on the country entered in the row.
- Observed behavior
- The formula in G4 refers to G4 itself and does not specify a complete lookup range on the VAT CHANGE worksheet, causing a formula error or circular reference warning.
Check which cell contains the country value, such as E4, and confirm that the VAT CHANGE sheet stores countries in column A and VAT rates in column B. Verify the actual data boundaries before entering the lookup range.
Replace the circular reference and complete the VLOOKUP range
Use this solution when the lookup formula refers to its own result cell or only names the source worksheet.
The first VLOOKUP argument must be the cell containing the country, not the cell containing the VAT result. The table argument must include both the country and VAT columns, with the worksheet name and cell range specified.
If the sheet name contains a space, enclose it in single quotation marks. Absolute references keep the lookup range fixed when the formula is copied.
In WPS Spreadsheets or Excel, click cell G4, where the VAT rate should appear.
Type =VLOOKUP(E4,'VAT CHANGE'!$A$2:$B$50,2,FALSE) in the formula bar or directly in G4.
Press Enter and check that G4 returns the VAT rate matching the country in E4.
If you fill the formula down, confirm that E4 changes to E5, E6, and so on while 'VAT CHANGE'!$A$2:$B$50 remains fixed.
Build the lookup formula from the worksheet data
Use this approach when you are unsure which cells contain the lookup key and return values.
Use WPS Spreadsheets to repair VLOOKUP formulas
WPS Spreadsheets can directly correct the circular reference, select the proper country cell, and create a complete VAT lookup range in an Excel-compatible workbook.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the formula error.
- 2. Edit the result cell: Select G4 and replace the self-referencing formula with =VLOOKUP(E4,'VAT CHANGE'!$A$2:$B$50,2,FALSE).
- 3. Verify the source range: Open VAT CHANGE and confirm that columns A and B contain the country and VAT rate data within the selected rows.
- 4. Save the compatible file: Choose File > Save As, select Excel Workbook (*.xlsx) if needed, and save the corrected spreadsheet.
Frequently Asked Questions
Why does referring to G4 in the VLOOKUP create a circular reference?
G4 is the cell intended to display the VAT result. If the formula in G4 uses G4 as its lookup value, the cell depends on its own result. Use the country input cell, such as E4, as the first VLOOKUP argument instead.
Why is 'VAT CHANGE' not a valid VLOOKUP table array by itself?
A worksheet name identifies a sheet but not the cells containing the lookup data. Specify a complete range, such as 'VAT CHANGE'!$A$2:$B$50, including both the country and VAT rate columns.
What does the number 2 mean in this VLOOKUP formula?
The number 2 tells VLOOKUP to return the value from the second column of the selected table array. In 'VAT CHANGE'!$A$2:$B$50, that is the VAT rate column B.
Should the last VLOOKUP argument be TRUE or FALSE for country names?
Use FALSE for an exact match when looking up a country name. TRUE uses approximate matching and generally requires the first table column to be sorted, which can produce incorrect results for unsorted country lists.
How can I keep the VAT lookup range from changing when I copy the formula?
Use absolute references with dollar signs, as in 'VAT CHANGE'!$A$2:$B$50. When the formula is copied, the input reference can change from E4 to E5 while the source range stays fixed.
