Formula Auditing & Errors
Fix and Prevent #REF! Errors in VLOOKUP, INDEX, and Structural Edits
Reproduce #REF! when col_index_num or INDEX coordinates exceed table dimensions, guard column bounds dynamically with COLUMNS/ROWS, and prevent broken column counts with MATCH or XLOOKUP.
Reproduce a live #REF! error without deleting a single column
Enter a 3-column SKU table in A2:C4 (SKU in A, Unit_Cost in B, List_Price in C). Try =VLOOKUP("SKU-20", A2:C4, 4, FALSE): even though "SKU-20" exists in A3, the formula immediately returns #REF! because col_index_num = 4 asks for a 4th column inside a 3-column range (A:C). The same #REF! error strikes =INDEX(A2:C4, 5, 2) when row_num = 5 exceeds ROWS(A2:C4) = 3, or when a collaborator deletes a referenced worksheet column directly.
Wrapping every formula blindly in IFERROR(..., 0) hides genuine structural breakage behind fake zeros! Instead, either derive the column position dynamically via MATCH(header, A1:C1, 0) / XLOOKUP, or check requested bounds explicitly against COLUMNS(table) and ROWS(table) so out-of-range coordinates report an explicit diagnostic tag.
| Failure Mode | Broken Formula | Root-Cause Fix |
|---|---|---|
| col_index_num > table width | =VLOOKUP("SKU-20", A2:C4, 4, FALSE) -> #REF! | Check col_num <= COLUMNS(A2:C4) or use XLOOKUP |
| INDEX row > table height | =INDEX(A2:C4, 5, 2) -> #REF! | Check row_num <= ROWS(A2:C4) before indexing |
| Hardcoded column count breaks after delete | Static 3 after deleting column B | Replace static 3 with MATCH("List_Price", $A$1:$C$1, 0) |
Bounds-Checked VLOOKUP Diagnostic Formula
Validate that requested column F3 lies between 1 and COLUMNS($A$2:$C$4) before calling VLOOKUP.
=IF(OR(F3 < 1, F3 > COLUMNS($A$2:$C$4)), "COL_OUT_OF_BOUNDS", VLOOKUP(F2, $A$2:$C$4, F3, FALSE))Header-Driven Lookup & Peer Suggestion Check
Match the header label in F4 ("List_Price" or "Unit_Cost") across $A$1:$C$1 so column shifts never hardcode a stale index. In a shared Arte Sheets workbook, invite a teammate in the Suggester role to propose replacing a fragile hardcoded col_index_num cell with MATCH, and test changing F4 from "List_Price" (90) to "Unit_Cost" (55) before accepting the suggestion.
=VLOOKUP(F2, $A$2:$C$4, MATCH(F4, $A$1:$C$1, 0), FALSE)Try the example
Use the original values or change them to test your understanding.