VLOOKUP, and why it breaks

A lookup answers one question: find this thing in a list, and give me something next to it. The price of a SKU. The owner of an account. The region for a zip code. Once you can do that, two tables become one.

VLOOKUP is the classic, and you'll see it in every inherited sheet:

=VLOOKUP(E2, A2:C6, 3, FALSE)

Read it as: find E2 in the first column of A2:C6, then return the value from the 3rd column of that range. FALSE means exact match.

It breaks in three predictable ways:

• Forgetting FALSE. Without it, VLOOKUP assumes the first column is sorted and returns a nearby row when there's no exact match. No error, just a wrong answer.
• It can't look left. The value you search for must be in the first column. If the owner sits to the left of the account name, VLOOKUP can't reach it.
• The 3 is hard-coded. Insert a column into the table and 3 now points at the wrong thing.

XLOOKUP fixes all three. You give it the column to search and the column to return separately, exact match is the default, and a 4th input says what to show when nothing matches:

=XLOOKUP(E2, B2:B7, A2:A7, "Not found")

INDEX and MATCH is the older pairing that does the same job and works in every version of every spreadsheet. MATCH finds the position, INDEX returns what's at that position. Its party trick is two-way lookups: a row MATCH and a column MATCH together.

Use XLOOKUP by default. Learn VLOOKUP so you can read other people's sheets, and INDEX/MATCH for two-way tables.