VLOOKUP


Explanation of the VLOOKUP function

The image below shows the 4 arguments that the VLOOKUP function is asking for, each color coded to help you follow along:

  1. lookup_value (blue)
  2. table_array (red)
  3. col_index_num (green)
  4. range_lookup (yellow)

VLOOKUP image_1

Video – How to use a VLOOKUP in Microsoft Excel

VLOOKUP explanation continued

1. lookup_value (boxed in blue)

In our example the VLOOKUP function is referencing the lookup_value in cell H3 (boxed in blue). The value in cell H3 is “Billy” and this is the value that our VLOOKUP function will be searching for in our table_array.

As an  alternative you could write “Billy” (has to be in quotations because it is text) directly into the VLOOKUP function and it would work the exact same. To do this you would replace H3 with “Billy”.

Note: It is easier to make changes to your lookup_value when your VLOOKUP function references the value in a cell because you can easily change your lookup_value and Microsoft Excel will automatically calculate the new result.

2. table_array (boxed in red)

In our example the table_array is B3:F22 (boxed in red) because this is our data range we want to search in. When using a VLOOKUP function you can either manually type your data range or select your data range using your mouse.

IMPORTANT: your lookup_value must exist in the left-most (the 1st column) in your data range. This means that your table_array always has to start with the column that includes the data for the lookup_value you are searching for.

3. col_index_num (numbered in Green 1 through 5)

This argument tells the VLOOKUP function which column to return the value from. In our example it’s 4 meaning the result will come from the 4th column in our table_array which is the “Quantity Harvested” column. You always start counting from left to right with the left-most column being column 1. If we wanted to bring back the “Quantity Sold” instead, our col_index_num would need to be 5.

4. range_lookup (underlined in yellow)

You have 2 choices depending on how you plan to use a VLOOKUP function:

  1. TRUE (or you can type 1) – will search for the closest match to your lookup_value
  2. FALSE (or you can type 0) – will search for an exact match to your lookup_value

Those 2 choices are manually typed by you into your VLOOKUP function.

Note: Always use the FALSE argument if you want to find an exact match against your lookup_value.

So what exactly is the VLOOKUP function doing?

  1. Step 1 – the VLOOKUP function searches for your lookup_value in the left-most column from your table_array (and only within the left-most column) starting with the 1st row and working its way down each row until it finds either:
    • an exact match if you used FALSE (or 0) as the range_lookup argument
    • the closest match if you used TRUE (or 1) as the range_lookup argument
  2. Step 2 – the VLOOKUP function will return the value in the col_index_num you provided. Once it finds your lookup_value it will stop on that row within your table_array and then count as many columns to the right based on the number you provided in your col_index_num to return the result.

Excel will generate an #N/A error if it doesn’t find your lookup_value. If we searched for “Bill” because Billy also goes by Bill, we would get an #N/A error because we were searching for an exact match.

In our example, the result is 400:

  • The function starts in cell B3 and says “Do you equal Billy?”. Since B3 = “Roger”, the answer is no.
  • Go down to cell B4.. “do you equal Billy?”. The answer is no.
  • Until the function gets to row 8 where the value does equal Billy.
  • The function stops on row 8 and counts 4 columns to the right (starting with the left-most column as 1)
  • Return the value 400

VLOOKUP image_2

 

Go back to Excel Tutorials.