Excel VLOOKUP Function: Why and How? | VLOOKUP EXAMPLE

Before we learn how to use Excel VLOOKUP function, we must learn when do we use Excel VLOOKUP function?

Excel VLOOKUP Function: Why and How? | Excel Tips & Tricks

Task: Imagine if you have to find when does train no. 6948 departs in the schedule chart above?

  • Your approach: You will scan the first column top to bottom. When you find the same train no., you stop there. Then you scan that row rightwards. Once you reach to the fifth column, you see the answer 23:00.
  • Observation: Please note that train no. 6948 is in the first column and it does not repeat. The train nos. are not sorted either.


Excel VLOOKUP function helps “pull” value from a table with the help of a matching ID or unique identifier.

In the Example given below, we need to find asset description of three IDs in Table 2. Table 1 has the details of ID wise asset description.

Excel VLOOKUP Function: Why and How? | Excel Tips & Tricks

You can already see that the ID 7951 refers to Computer Table. That’s because this is a simple example. However, using Excel VLOOKUP function, you can easily find asset descriptions of 1000s of such IDs in less than a minute. This function is very helpful when you have a large amount of data.

I think finding Excel related query and searching on google, not finding the right answer, don’t you think its quite frustating? Its better you enroll in Beginner to Advanced Excel Course & stop for looking for related query.

There are four pieces of information that you will need in order to write the

Excel VLOOKUP function:

Excel VLOOKUP function

  • lookup_value: Refer marker “A” in the picture above. First, parameter lookup_value is the unique value or ID Code whose Asset Description is to be found. g. 7951. Some refer such ID code as a primary key.
  • table_array: Refer marker “B” in the picture above. It is the table whose first column must contain the lookup_value or primary key. One of the columns of the table_array will contain details of Asset Description.
  • col_index_num: Refer marker “B” in the picture above. The table_array chosen above has 2 columns. The second column contains Asset Description. Hence, col_index_num is 2.
  • range_lookup: 0 or FALSE indicates an exact match. It means if your primary key in the example above is 7951, then provide asset description only and only if the same key is found in the first column of table_array selected. Else #N/A! an error will be given. We will discuss the situation when to use TRUE or 1 in another blog.

Let’s walk through the process of writing Excel VLOOKUP function like a Pro.

Beginner Excel VLOOKUP function

  • Step 1: Refer VLOOKUP example picture given above. Type an = (equal sign) and begin typing “VL” to start an Excel VLOOKUP function. Excel displays a dynamic drop-down list of valid functions starting with the letters VL. Choose the desired function from such list and press TAB key. This will autocomplete the Excel VLOOKUP function =VLOOKUP(

Excel VLOOKUP function =VLOOKUP(

  • Step 2: Refer VLOOKUP example picture given above. Choose the cell F4. It contains the ID code 7951.
    Excel VLOOKUP function
  • Step 3: Refer VLOOKUP example picture given above. Choose the table range where ID codes are given along with the asset description in subsequent columns. The first column of the table_array MUST contain the lookup_value or the primary key.


  • Step 4: Refer VLOOKUP example picture given above. As soon as table_array is chosen, press function key F4 to lock the range selection with four $ signs. This ensures that as the function is copied and pasted below, the range remains B2:C6 for all. Not doing so may give errors instead of correct answers. Watch the VLOOKUP example picture below. The initial range B2:C6 has changed to B4:C8.


A reason for the error (#N/A): As the Excel VLOOKUP function is copied and pasted below, the table_array changes from B2:C6 to B4:C8. This means that the ID code 7082 is being looked up in the revised range. The revised range does not contain the ID code 7082.


  • Step 5: Refer VLOOKUP example picture given above. Now we need to specify the correct column sequence number from the table array. Column number 2 refers to the second column of the chosen table_array. Col_index_num can be 2, 3, 4 … n. It cannot be a negative number or a fraction. E.g. using -1 will not give Serial No. 04.


Related readings on: 5 Powerful Tricks to Format cells in Excel

  • Step 6: True (1) and False (0) refers to approximate and exact match, respectively. In the VLOOKUP example picture given above, we want the exact match. E.g. ID 7951 should give the answer “Computer Table”. If the ID 7951 were not present in Table 1, then #N/A will be the answer. It will not look for any close match such as ID 7950 or 7952.

Next Step: Simply drag the cell down using Autofill handlebar. This will help give answers to all the three ID codes.

Extra Caution: The cell F6 has been changed to ID code 7947. This ID code is not given in Table 1. Yet, it gives the answer “Computer Chair”. This is because the function is looking for an approximate match. If nothing is mentioned in the fourth parameter i.e. range_lookup takes the value TRUE (1) or an approximate match. This clearly is an incorrect answer. You can also improve your productivity with our Excel VBA Tutorial. Also, try out our impressive Excel macro tutorials to gain powerful insights on Excel VBA.


Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018