VLOOKUP() means vertical lookup. It is a powerful built-in function you can use to quickly search for a value in a spreadsheet.
VLOOKUP() searches for a value in a vertical manner across the sheet – unlike the
HLOOKUP() function which does it horizontally.
VLOOKUP(), make sure every row in your worksheet has an ID. The IDs must also be arranged in ascending order. This makes sure Excel is not confused during the process of returning a value from a search.
In this article, I will show you how to use the
VLOOKUP() function by explaining the values you should put in the function. We'll also look at two different examples.
VLOOKUP() Formula and its Values
In Excel, you do almost everything with a formula – and
VLOOKUP() is not an exception.
Below are the values that the
VLOOKUP() function takes:
VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])
- lookup_value: the cell that has the value you want to search for. It’s always on the left. For example, A5.
- table_array: the location where you think the value is and where you want Excel to search for the value. For example, A1:D10
- column_index_num: the column where the value is located. For instance, 4
- [range_lookup]: You can only specify TRUE or FALSE for this. TRUE means approximate match and FALSE means exact match.
How to Use
VLOOKUP() in Excel
VLOOKUP() to search for some value in Excel, you need to use the formula and enter the individual values discussed in the previous section.
To show you how to use
VLOOKUP, I’ll be using the table below. It’s a table showing some fictional footballers (soccer players), their ages, clubs, and career goals scored.
VLOOKUP() Example 1
In the table, I want to see the number of career goals scored by Kat Katongo (
A5). We can use VLOOKUP() in this case. This example does not require an ID for all the entries.
Step 1: Type a representative name in an empty cell:
Step 2: It makes sense to put the career goals right in front of the rep cell, so I’ll highlight the cell:
Step 3: Enter the formula in the formula bar:
The formula I’m using is
=VLOOKUP(A5, A1:D10, 4, FALSE):
- A5 is the lookup_value
- A1:D10 is the table_array
- 4 is the column_index_num – because that’s the column for the career goals
- FALSE is the [range_lookup] because I want an exact match.
And boom! 180 appears in the cell:
To make things clearer for you, the GIF below shows how I entered the formula:
VLOOKUP() Example 2
In this example, I want to just enter an ID and see the career goals of the player. This means I need to assign an ID to the individual footballers.
Step 1: Now, I want to start by looking up the career goals of the player with an ID of 9. So, I prepare some more entries on the right.
Step 2: To look up the career goals of the player with an ID of 9 (Baba Ali), I’ll select the cell I want it to show in and click the formula tab. Once I do that, a prompt will show up.
Step 3: Select
VLOOKUP and click OK. If you don’t see
VLOOKUP() there, search for it and select it.
Step 4: Another popup showing the inputes to enter the values of the formula will show up:
Step 5: Enter the values one by one:
- I’ve entered
I3for the lookup value because that’s where I put the ID of 9
A1:E10for the table array because that’s the area I want to lookup
5for the column index because that’s the column containing the career goals of the players
FALSEas the range_lookup because I want an exact match
When I pressed
OK, the career goals of Baba Ali (the player with ID 9) shows up:
To make things clearer to you, this is how I entered the formula:
Any time I change the ID and press
ENTER, the career goals of the player with that ID shows up:
VLOOKUP() is a powerful Excel function that can help you search for any value in a worksheet – whether small or large. That’s why I wrote this article to help you get started with it.
If you find this article helpful, kindly share it so it can get to others.
Thank you for reading.