Lookups and data cleaning
You often need data from another table: your sales sheet has the product name, and you want its cost from the price list. In SQL you'd JOIN; in Excel you use lookup functions:
| XLOOKUP | The newest and simplest: =XLOOKUP(D2, Prices[Product], Prices[Cost]). Finds a value in one column and returns the match from another, in any direction. Available in Microsoft 365 and Excel 2021 or later. |
| VLOOKUP | The oldest and best known: =VLOOKUP(D2, A2:C10, 3, FALSE). Searches only the first column and needs a column number. Don't forget `FALSE` for an exact match. |
| INDEX + MATCH | The classic flexible combination that works in every Excel version. |
Data cleaning takes most of an analyst's actual time. Real data from an ERP or staff spreadsheets always has problems:
| Problem | Example | Fix |
|---|---|---|
| Extra spaces | “Riyadh␣” doesn't match “Riyadh” | TRIM |
| Duplicate rows | The same invoice recorded twice | Remove Duplicates |
| Inconsistent values | “Riyadh”, “Riyad” and “RUH” | Standardise via a lookup list or Find & Replace |
| Numbers stored as text | SUM returns zero | Convert to numbers (VALUE) |
| Missing values | A blank region | Fill from the source, or exclude and note it |
Watch outThe analyst's rule: never edit the original data. Work on a copy and document every cleaning step. Tools such as Power Query record the steps and replay them whenever the data refreshes.