Established onExcel 365 and 2021: all three availableExcel 2019 and earlier: no XLOOKUPGoogle Sheets, web: XLOOKUP since 2022
Three approaches, in the order they arrived, distinguished by what breaks them rather than by syntax.
The positional one
The most used lookup in this field takes a table and a number saying which column to return, counting from the left edge of the table. It searches only the first column of that table.
Two consequences follow. It cannot return anything to the left of the key, so a table has to be arranged around the lookup rather than around the data. And the column number is positional: inserting a column inside the table shifts everything to its right, the formula continues to request the same numbered column, and it now returns different data without any indication.
That second point is the important one. A colleague inserting a column is an ordinary act, and it silently changes every formula of this kind pointing at the table.
The matched one
Splitting the job in two removes both problems. One function finds the position of the key in a column; another returns the value at that position in whichever column you name. The returned column is a reference, not a count, so insertion moves it correctly, and it may sit anywhere relative to the key.
The cost is that it is two functions and less obvious to read, which is the honest reason it never displaced the first one despite being better for thirty years.
The current one
The newer function does the same job in one call, defaults to exact matching, takes an argument saying what to return when nothing matches, can search from either end, and returns whole rows or columns rather than single cells.
It is the right answer where it exists, and where it exists is the whole difficulty. It is present in Excel 365 and Excel 2021 and in Google Sheets since 2022, and absent from Excel 2019 and everything before it. A file using it, opened in an older version, shows an error rather than a value.
Choosing between them in practice
If everyone who will open the file has a current version, use the new one. If that cannot be guaranteed, use the matched pair: it works everywhere back to the nineteen-nineties and has none of the positional fragility.
Use the positional one only when editing a file that already uses it and a wholesale change is not warranted, and in that case anchor what you can: refer to a named table rather than a rectangle, so at least the range survives.
What all three share
None of them tells you that a key was absent unless you ask. The first two return an error value for a missing key only in exact mode, and in approximate mode they return a value from another row, which the next entry is entirely about.
And all three return the first match. Where a key appears twice, the second row is invisible, and nothing counts the duplicates for you. A lookup against a table with unnoticed duplicate keys is one of the more common ways for a total to be quietly wrong, and it costs one cell to check: count the rows and count the distinct keys, and require them to agree.
What we cannot verify
The behaviours and the availability by version are documented by both vendors and were confirmed on current builds. Availability in Google Sheets changed during 2022 and the exact rollout dates by account type are not published in a form we could check. Claims here about older Excel versions rest on the vendor's documentation rather than on testing, since we do not have those versions to hand.
The lookup that should never be used
There is an older single-word function, predating the ones above, which has no exact-match option at all and always behaves approximately. It survives for compatibility and appears in old files.
Encountering it in a sheet you have inherited is worth treating as a finding rather than a detail: it means the file predates the modern alternatives, and everything else in it is likely to be of the same vintage.
What to check before rewriting old lookups
Whether the table has duplicate keys. Replacing a positional lookup with a better one preserves the first-match behaviour, so a rewrite fixes the fragility and not the duplicates, and the totals will not change, which is easy to read as confirmation that everything was fine.
The version of this that is not a lookup at all
Where two tables must be combined on a key, a lookup per row is the spreadsheet answer and a join is the right one. Both products can do the join properly: the query tool in one and a query function in the other, and both produce a result that is one object rather than a column of independent formulas.
Worth knowing because a sheet with ten thousand lookup formulas is slow for a reason that cannot be optimised away, and the alternative is not more clever formulas.
In short
- The positional lookup counts columns from the table's left edge and searches only the first.
- Inserting a column inside the table changes what every such formula returns, silently.
- The matched pair uses a reference rather than a count and works back to the nineties.
- The newer single function defaults to exact matching and handles absence explicitly.
- It is absent from Excel 2019 and earlier, where a file using it shows an error.
- All three return the first match, so unnoticed duplicate keys go unreported.