A Simple Excel VLOOKUP example
In this example, I will show you a simple way to use VLOOKUP that even a beginner would understand. VLOOKUP represents Vertical Lookup. That means Excel will look for a value on the vertical, from top to bottom, and return the corresponding value from the same row where the value is found.
Simple Enough? Let’s see it in action
In the table below in cell B2, I’m looking to write a VLOOKUP formula to get the color of the fruit Banana. As you can see, I have a reference table with fruit colors. Banana is in the table and the color is “Yellow”. So I want the result of my formula to be “Yellow”
How would I get this result just by looking at the image below?
I would glance at the “Fruit Colors Table” in rows E and F. I notice the fruits are in Column E so I go down the list, vertically, from top to bottom (Vertical Lookup). When I find it, I will move my eyes to the right one cell to get the color. Now let’s write that in Excel using VLOOKUP.
VLOOKUP in Excel
When I start typing the formula =VLOOKUP( Excel will give me all the parameters that I need to add to this formula
The first parameter is the lookup_value. This is the value you are looking for. I’m looking for the value “Banana” so the first parameter is A3 (Cell A3 contains the value “Banana”
Notice how when I start writing a Cell reference (or picking it with the mouse) the cell contents gets highlighted in the same color as the cell reference color in the formula (blue)
Let’s put a comma and move to the next parameter of VLOOKUP. The next parameter is the table_array. This is the table containing the fruits and the colors. I will use the entire column E and F together for that. To select, click on the top edge where E is written, select with the mouse and with the click pressed, drag to column F.
IMPORTANT: For VLOOKUP to work correctly, it is important that the column containing the lookup values, in our case the fruits column, needs to be at the left side of the array. In other words, you can’t use VLOOKUP if the fruits column is at the right of the color column. Also, you can’t use it if you select the array D:F
But good news, Excel has a new function called XLOOKUP that can work around these restrictions.
Notice how the second parameter is now an array E:F and it is highlighter in red, the same as the actual highlight of the table in column E and F. Let’s add another comma and move to the next parameter.
This next parameter is called col_index_num. This is the column index number where I find my result. If the column with the fruits is column 1, then the column containing colors is color 2. So let’s add a 2.
This means that Excel will look in the second column for a result when it finds “Banana” in the first column.
And now, to finish off, we have a last parameter called range_lookup. Most of the times this parameter will need to be false because we are looking for an exact match.
And here is the result of the VLOOKUP function in B3
I hope this makes the VLOOKUP function as clear as possible. For more VLOOKUP and XLOOKUP posts visit these references
How to use VLOOKUP in Excel 2020
How to use XLOOKUP in Google Sheets
