VLOOKUP fails for two different reasons, and the fixes are not the same. The first is structural. It only looks right, and the column index is a hardcoded number that breaks the moment someone inserts a column.
The second is dirtier. Your lookup key does not match the table exactly. "IBM" is not "International Business Machines", and VLOOKUP returns #N/A on every row like it.
The Structural Fix (INDEX MATCH or XLOOKUP)
For the first problem, the answer is well known. INDEX and MATCH look in any direction and do not care about column position.
=INDEX(C:C, MATCH(A2, B:B, 0))XLOOKUP is the newer, cleaner version of the same idea.
=XLOOKUP(A2, B:B, C:C)Both are real upgrades over VLOOKUP. But notice they still need an exact match on the key. They fix where the formula looks, not whether the value is there in the first place.
The FITS Way (Match by Meaning)
When the key is messy, no lookup function can help, because there is no exact match to find. This is where FITS matches on meaning instead of characters.
=FITS("Match this company to the closest name in the list. Company: " & A2 & ". List: " & JOIN(", ", Accounts!B2:B500))Sometimes you do not even have a table. You just need a known fact attached to each row. VLOOKUP needs a reference sheet for that. FITS does not.
=FITS("What country is this city in: " & A2)That is enrichment without a lookup table. The value comes from what the model knows, not a range you had to build and maintain.
When to Use Each
When your keys are clean and consistent, use XLOOKUP or INDEX MATCH. They are fast, free, and exact. Reach for =FITS() when keys arrive with typos, abbreviations, or variant spellings, or when the answer is general knowledge you would otherwise paste in by hand. If the keys are close but inconsistent, our normalize data guide can tidy them before a normal lookup. For pulling reference values off the web into a sheet, see how to pull data from a website into Google Sheets. Fuzzy matching is one of the jobs a formula never handled, and the full set is in automating Google Sheets tasks you used to need regex for.
Stop Getting #N/A on Every Messy Key
FITS puts plain-English AI formulas inside Google Sheets. Match by meaning, enrich without a table, and skip the #N/A pile. Free tier included.