Auto populate means one thing in practice. A value lands in one cell, and a related value should appear in another without anyone typing it.
Category from product. Region from city. Owner from account name. Sheets can do all of these, as long as you have already written down every possible answer.
The Old Way (VLOOKUP Inside ARRAYFORMULA)
The standard advice is a mapping tab plus one array formula at the top of the column.
=ARRAYFORMULA(IF(A2:A="", "", IFERROR(VLOOKUP(A2:A, Mapping!$A$2:$B$200, 2, FALSE), "Unmapped")))This works on the day you build it. Three failure modes show up later.
First, the array needs an empty column to spill into. One stray note pasted into B47 returns a #REF! error and kills the whole column.
Second, rows arriving from a Google Form get inserted rather than appended. The insert pushes your range boundaries around, so new rows quietly come back blank.
Third, and worst, any value not already in the mapping tab returns nothing useful. A human reading "Stainless Prep Table 48in" knows it is Kitchen. VLOOKUP returns "Unmapped" until someone adds the row.
The FITS Way (Just Ask)
With FITS, you describe the rule once. There is no mapping tab to maintain.
=FITS("Return the department for this product. Valid departments: Kitchen, Outdoor, Office, Cleaning. Return only the department name. Product: " & A2)A product you have never sold before still gets a department. The constraint list keeps the answer inside your allowed values, so the column stays filterable.
If a mapping table already exists and you want to keep it as the source of truth, hand it to the formula as context instead of as a strict lookup.
=FITS("Use this reference list to assign a department. If nothing matches exactly, pick the closest fit. List: " & JOIN("; ", Mapping!A2:A200) & " Product: " & A2)Copy the formula down the column like any normal formula. Each row evaluates on its own, so a pasted note in one cell cannot break the other 500.
When to Use Each
For a fixed list of SKUs that never changes, VLOOKUP is free and instant. Reach for =FITS() when new values arrive constantly, when the match needs judgment, or when "Unmapped" rows keep landing on someone's desk. The same pattern powers matching messy keys without VLOOKUP, dependent dropdowns without INDIRECT, and sorting free text into categories. The full tour is in automating Google Sheets tasks you used to need regex for.
Retire the Mapping Tab
FITS puts plain-English AI formulas inside Google Sheets. Describe the rule once and let every new row fill itself. Free tier included.