Theory
Fetch the price from another sheet
Aryan has a price list (item names and prices) on one sheet, and a sales sheet listing items sold. He needs each sale's price pulled automatically from the price list, matching by item name, for 5000 rows.
Typing them is impossible. Copying by hand is error-prone. This 'look up a value in another table by its key' is the single most useful function in business Excel: VLOOKUP.
It is also the one with the most infamous trap: a tiny fourth argument that, left wrong, returns plausible but incorrect data silently. Master this and you master the elective.
Theory
Looking up a name in a directory
A phone directory is sorted by name; you find the name, then read across to the number. VLOOKUP does exactly this: you give it a key (the item name), it finds that key in the first column of a table, then reads across to the column you want (the price). The catch: a directory lookup only works if you match the name exactly, 'Riya Shah' is not ' Riya Shah'. VLOOKUP's exact-match switch is that same demand.
Theory
VLOOKUP anatomy
The general form, then a real example:
=VLOOKUP( lookup_value , table_array , col_index , [exact?] )
=VLOOKUP( A2 , PriceList!$A$2:$B$100 , 2 , FALSE )
Reading the example: find A2 (the item name) in the locked price-list table $A$2:$B$100, whose key sits in column 1; return the 2nd column (the price); and FALSE demands an exact match. Never leave off that last argument.
Theory
The four arguments, and the deadly last one
- lookup_value: what to find (the item name in A2).
- table_array: the table to search; the key must be its first column. Lock it with
$so it does not drift. - col_index_num: which column to return, counted from the table's left (price is column 2).
- range_lookup: FALSE = exact match (what you want), TRUE = approximate (needs the first column sorted ascending; used for grade bands).
The trap: omit the fourth argument and it defaults to TRUE, doing an approximate match that can return the wrong item's price with no error at all.
Quiz
Aryan writes =VLOOKUP(A2, PriceList, 2) and forgets the FALSE. His item list is unsorted. What is the risk?
- It does an APPROXIMATE match and can silently return the wrong price
- It shows #N/A for every row
- It is perfectly fine, FALSE is the default
- It returns the item name instead of the price
Show the answer
It does an APPROXIMATE match and can silently return the wrong price
Omitting the fourth argument defaults it to TRUE (approximate match), which assumes the first column is sorted ascending. On unsorted data it returns whatever it lands near, a wrong price, with no error to warn you. This silent-wrong-answer is the most dangerous VLOOKUP bug. Rule: always end VLOOKUP with FALSE for exact matching.
Think first
VLOOKUP cannot look left
Aryan's table has the price in column A and the item name in column B. He wants to find an item name and return its price (to the LEFT). Why does VLOOKUP fail here, and what solves it?
Show the answer
VLOOKUP can only search the first column and return something to its right, it cannot look left. With the name in column B and price in column A, the price is to the left of the key, so VLOOKUP cannot fetch it. Solutions: rearrange columns (key first), use INDEX/MATCH, or best, XLOOKUP, which looks in any direction, matches exactly by default, and needs no column-index counting. This left-lookup limitation is exactly why XLOOKUP was created.
Watch out
Where marks leak
The big one: omitting FALSE (exact match), causing silent wrong answers. Forgetting the key must be the first column of the table, and that VLOOKUP cannot look left. Not $-locking the table_array (it drifts when copied, Unit 2 bug). Miscounting col_index (from the table's left, and it breaks if you insert a column). And #N/A means not found, wrap with IFERROR/IFNA. HLOOKUP is the horizontal twin; XLOOKUP is the modern fix. This topic is the elective's richest source of marks.
Theory
The most valuable Excel skill you will learn
VLOOKUP (and XLOOKUP) is what employers mean when a job ad says 'must know Excel'. It connects tables, exactly the relational-join idea from your BCA105 database unit, done in a spreadsheet. Combine it with TRIM (clean the key first) and IFERROR (handle #N/A) for bulletproof lookups. Next lesson: date and time functions revisited, DATEDIF for age and tenure, before the analysis power tools.
Summary
Key takeaways
- VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) fetches a value from another table by matching a key.
- It searches the FIRST column and returns a column to its RIGHT; it cannot look left.
- ALWAYS use FALSE (exact match) as the fourth argument; TRUE (approximate) needs sorted data and can return wrong results.
- Lock the table_array with $; #N/A means not found (wrap with IFERROR/IFNA).
- HLOOKUP is the horizontal version; XLOOKUP is the flexible modern replacement (any direction, exact by default).
- Memory hook: find the name in the directory's first column, read across; match EXACTLY.