The Lookup Wizard
VLOOKUP looks like alphabet soup, but the idea is simple: find this value in that table, bring back the matching information. Product code in, price out. Names in, phone numbers out. One function replaces minutes of manual searching — and it is far less scary than it looks. Pair with the pivot table basics for full spreadsheet confidence.
TL;DR / Quick Fix
Quick fix: VLOOKUP finds a value in a table and returns the matching info — like a price for a product code. Keep your lookup column on the left, use exact match, and wrap it so errors show a friendly message. One function replaces minutes of manual searching.
Follow these steps
- Understand the idea. You have a table: product codes in one column, prices in another. VLOOKUP takes a code, finds its row, and returns the price. Lookup value in, answer out.
- Keep the lookup column leftmost. VLOOKUP searches the first column of your table and returns from columns to its right. Put the thing you search by — the code, the name — in the leftmost column.
- Write the basic formula. Use VLOOKUP with four parts: what to find, the table range, which column to return, and FALSE for an exact match. Exact match avoids near-miss surprises.
- Use exact match. That final FALSE means match exactly or report an error. Leaving it out invites wrong answers that look right — the most dangerous kind.
- Handle errors gracefully. Wrap the formula so missing values show a friendly message like not found instead of an ugly error code. Your spreadsheet stays readable.
- Copy down carefully. Lock the table range with dollar signs before copying the formula down a column, or the range slides and everything breaks.
What NOT to do
- Do not skip the exact-match argument. Omitting it is the number one cause of VLOOKUP returning wrong answers silently.
- Do not put the lookup column anywhere but first. VLOOKUP cannot look left — the search column must be the leftmost.
- Do not ignore the error codes. An error means no match found, which is information — investigate, do not just hide it.
- Do not use VLOOKUP for huge messy tables. Clean the data first; the function is only as honest as its table.
Frequently asked questions
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP is the newer, more flexible sibling that can look in any direction. VLOOKUP remains the classic everyone recognizes.
Why does my VLOOKUP return the wrong value?
Usually approximate match instead of exact, or the lookup column is not leftmost. Check both first.
Can it look up text, not just numbers?
Yes — names, codes, any text. Just make sure the text matches exactly, including no extra spaces.