Formula Errors

Fix an Excel IF and VLOOKUP Formula Returning Zero

Maira MehtabMaira Mehtab Nov 20, 2026 870 views

Question details

The user needs a formula that looks up G119 first, tries H119 if the first lookup fails, and returns zero only if both lookups fail.

Fix an Excel IF and VLOOKUP Formula Returning Zero
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses VLOOKUP to search a table on the New Holding QS sheet using two possible lookup values.
Observed behavior
The formula returns zero or an #N/A error instead of trying the second lookup as intended.
Before you start

Confirm that the lookup values are in column R of the New Holding QS sheet and the values to return are in column T. Check that G119 and H119 contain the values you want to search for.

Solution 1Recommended

Use Nested IFERROR to Try Both VLOOKUP Values

Try G119 first, then H119 if the first lookup returns an error.

1
Select the formula cell

In Excel, select the cell containing the existing IF and VLOOKUP formula.

2
Replace the formula

In the formula bar, enter =IFERROR(VLOOKUP(G119,'New Holding QS'!R:T,3,FALSE),IFERROR(VLOOKUP(H119,'New Holding QS'!R:T,3,FALSE),0)) and press Enter.

3
Check the result

Compare the result with columns R and T on the New Holding QS sheet. If both lookups fail, the formula returns zero.

Use Nested IFERROR to Try Both VLOOKUP Values
How errors and zero values are handled: IFERROR catches all lookup errors, not just #N/A. If the first lookup succeeds and returns an actual zero, the formula keeps that zero rather than trying H119.
Work with formulas in WPS Spreadsheets

Fix the Two-Value VLOOKUP in WPS Spreadsheets

Open the workbook in WPS Spreadsheets and use nested IFERROR and VLOOKUP functions to check G119, then H119.

  1. 1. Open the workbook: Open WPS Spreadsheets, choose Open and select your Excel workbook.
  2. 2. Select the result cell: Click the cell containing the existing lookup formula and select the formula bar.
  3. 3. Enter the nested formula: Enter =IFERROR(VLOOKUP(G119,'New Holding QS'!R:T,3,FALSE),IFERROR(VLOOKUP(H119,'New Holding QS'!R:T,3,FALSE),0)) and press Enter.
  4. 4. Verify and save: Check the result against columns R and T on the New Holding QS sheet, then choose Save.
Try a second lookup value when the first lookup failsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with spreadsheet formulas in a free Office alternative
QA img-9

Frequently Asked Questions

Why does the original formula return zero before checking H119?

If the error test checks the G119 lookup and returns zero when it fails, the H119 lookup is never reached. Nest the H119 lookup inside the error-handling result instead.

Why does VLOOKUP return #N/A when the value appears to be present?

With FALSE as the last argument, VLOOKUP requires an exact match in the first column of the range. Check column R for extra spaces and make sure numbers and text are stored consistently.

Will the formula try H119 if G119 returns a zero?

No. A successful lookup that returns zero is not an error, so IFERROR keeps the zero. The fallback to H119 runs only if the G119 lookup returns an error.

What does the 3 mean in VLOOKUP(G119,'New Holding QS'!R:T,3,FALSE)?

It selects the third column of the lookup range R:T, which is column T. VLOOKUP searches for G119 in the first column, R, and returns the corresponding value from T.