XLOOKUP can return a value from a separate column using an exact key. Establish aligned ranges and a missing-key policy before filling the formula down a report.
Set up a small known lookup
Suppose A2:A4 contains P01, P02, and P03, while B2:B4 contains 10, 20, and 30. With P02 in E2, =XLOOKUP(E2,A2:A4,B2:B4,"Not found",0) should return 20.
The lookup range is one column and the result range has matching height. The explicit 0 requests an exact match. This example uses ordinary Sheets syntax, not a BigQuery-specific variation.
Keep the ranges aligned when copying
If several report rows use the same source, anchor the source ranges as needed, for example $A$2:$A$4 and $B$2:$B$4. Leave the report’s search-key reference relative when each row has its own key.
Check the first and last source records. Starting one return range a row lower can associate a valid key with the wrong value even though the formula produces a plausible result.
Verify absence and duplicates
Try an absent key and confirm the chosen missing-value label appears. Do not use an approximate-match mode merely to eliminate missing results. Check whether the lookup keys are unique; the first matching result may be inappropriate when several records share an ID.
Compare key types and whitespace if an expected match fails. Keep the source unchanged until the discrepancy is understood. A successful exact lookup answers one defined matching question; it does not establish that the returned price, status, or account record is the current authoritative version.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.