Ravindra BagaleCourses & study guides

4. Lookup Functions

4.1 VLOOKUP with Exact Match

By Last updated:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value – what to find (e.g. the Store ID in the order row)
  • table_array – the lookup table; the lookup value must be in its first column
  • col_index_num – which column of the table to return (1 = first column)
  • range_lookup – FALSE (or 0) = exact match; TRUE (or omitted) = approximate

Steps in Excel – bring City into Orders

  1. In Orders, Store ID is in column G. Click the first empty column, e.g. P2.
  2. Type =VLOOKUP(G2, then go to Stores, select A2:F8, press F4 to lock it: Stores!$A$2:$F$8.
  3. Type ,3,FALSE) (City is the 3rd column) › Enter.
  4. Double-click the fill handle to copy down.
Formula Result
=VLOOKUP("BLK-NSK-01",Stores!$A$2:$F$8,3,FALSE) Nashik
=VLOOKUP("AMN-NGP-01",Stores!$A$2:$F$8,5,FALSE) Amir
=VLOOKUP("BLK-PUN-09",Stores!$A$2:$F$8,3,FALSE) #N/A (not in master)

Ravindra Bagale's Tip

The most common VLOOKUP mistake is not writing the final FALSE. Then Excel does an approximate match and gives a wrong answer that "looks right" – without any error! For an exact match, always write FALSE or 0, and put $ on the table_array.

Practice task

Bring Area, City Manager and Manager Email into Orders with three VLOOKUPs. Add a Store ID that does not exist and observe the #N/A.