Lookup Trace

Location on ribbon: Tab DVD Addin → Group Formula → Menu Trace

Command ID: btnGetLookup

What it does

This command jumps straight to the source cell that the lookup formula in the current cell pulled its value from. Instead of opening the lookup table, hunting for the code by hand and counting columns, one click switches Excel to the right sheet and selects the exact cell.

It pays off on cost estimates, BoQ sheets and unit-price tables with hundreds of rows looked up from a norm sheet: when you need to know "where did this 1,850,000 unit rate come from?", this is the fastest answer.

How to use

1. Select a single cell that contains a lookup formula (VLOOKUP, HLOOKUP, XLOOKUP or INDEX).
2. Go to tab DVD Addin → group Formula → menu Trace → click Lookup Trace.
3. The command parses the formula, re-evaluates the lookup value and resolves the matching source cell.
4. Excel activates the sheet holding that cell and selects it, so the original value shows in the formula bar.
5. If the current cell has no formula, a notice appears — "Please select a cell that contains a formula!" — and nothing else happens.

Example

On sheet BOQ, cell F12 holds:

=VLOOKUP(B12,'RATES'!$A$5:$E$400,4,0)

where B12 is item code AF.11220 (M250 foundation concrete). Put the cursor on F12 and click Lookup Trace: Excel switches to sheet RATES and selects D137, the cell that stores the unit rate for AF.11220. From there you can verify the norm reference, the pricing date and whether a row was edited by mistake.

Notes

• Only four functions are recognised: XLOOKUP, VLOOKUP, HLOOKUP, INDEX. Formulas built on LOOKUP, a bare MATCH, OFFSET, INDIRECT or custom functions cannot be traced.
• When several lookups are nested in one formula, only the first recognised one is followed, in the order XLOOKUP → VLOOKUP → HLOOKUP → INDEX.
• Matching is always performed as an exact match; if the original formula used approximate matching, the cell you land on may differ from the real result.
• A formula returning #N/A, or a lookup range that cannot be evaluated, produces the notice "Could not trace target" instead of a jump.
• The command changes the active cell and sheet but never edits data. Press Ctrl + Alt + ← or Excel's Back navigation to return.

Related

• Formula View — breaks a formula into clickable tokens so you can inspect each argument before tracing.
• Dependents — the opposite direction: which cells reference the current one.
• Direct Ref — generate cross-table reference formulas keyed on a shared column.