- This topic has 0 replies, 1 voice, and was last updated 1 year, 4 months ago by Aruorihwo.
- February 3, 2020 at 4:52 pm #85079Spectator@aruorihwo
As promised in our previous post about how to create a simple inventory (if you missed it check it out here), we would be looking at the VLOOKUP function in excel. We would talk about what it is, what it is used for and how it is used.
VLOOKUP is a very powerful tool, and knowing how it works can save you a whole lot of stress, especially if you deal with a lot of data. The great thing about the function is how easy it is to use. Once you are able to understand it, then you are on your way to extracting data like a pro.
What is it?
VLOOKUP means Vertical Lookup. Lookup values must appear in the first column of the table with lookup columns to the right. That means whatever is on the first column of your table is what you use as your search parameter. Let’s use the tables below to illustrate what we are trying to say:
Table one: Using Customer ID
Table Two: Using Customer Name
In table one, you have to use the Customer ID to search for other data because that’s the first column. While in table two, the first column is the Customer’s Name.
To look up a particular data, the entry needed is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]. The formula has four arguments and each has to be entered correctly in other to get what you are looking for. Let’s take a closer look at each argument.
The value here is what you want to look up. In the case of table one above, the value is the Customer ID. We are going to assume we have a lot of customers, that means we have a lot of IDs, so in that case, we would use a blank cell in the formula. That way, whatever we input into that cell is what would automatically be looked up.
This refers to the table you want VLOOKUP to pull out the data from. If you have multiple tables or a very large table, it would be easier for you to name the table instead of trying to select the entire table.
Column Index Number
This refers to the column you want the data pulled out of. For example, if you want the customer name associated with the ID number entered, then from our example above, you would enter number 2. Please note, that you are counting from the first column of the table and not the first cell. For instance, if your table starts from cell G2, the correct column number is still 2 and not 7.
Finally, the range lookup refers to whether you want an exact match or an approximate match. “False” is for an exact match while “True” is for an approximate match. Most times, you are looking for an exact match when looking up data in excel.
Let’s see an example;
The column for the ID number is left blank, that way whatever ID number we input into that cell would automatically give us the required information.
To get the customer name associated with that id:
=VLOOKUP(I2,A1:D6,2,FALSE). I2 is the blank cell as explained above, A1:D6 is the table area I want to search for my information, 2, is the column number where the name appears and “false” means I want an exact match. For product and price, the only thing different in the argument is the column number which is 3 and 4 respectively.
You can also look up data from multiple excel sheets, doing this is the same as above, except this time, in order to put the second argument just click on the desired sheet and excel would automatically input the name of the sheet eg
sheet1!(unnamed sheets) or
details!(sheet name), then you select the table, and you would have
details!A1:D6. If your table is named already, you don’t have to switch sheets, you just type in your table name and column number
=VLOOKUP(I2,customers,2,FALSE). To rename your table, select the table area, and click on the box beside the formula bar.
VLOOKUP + IF function
This combination can be used to look up data on more than one table. You can use the combination to check whether a condition is met and return one lookup table if true and another lookup table if false. For example, from the table below, if you want to pull up the bonus amount a sales rep would get depending on the number of sales made, then you would combine VLOOKUP with IF function. In the example, there are three tables, one for the sales details, one for the bonus for each product (banana and apple)
To get the bonus, we would input in cell F2;
The argument above states that, we are looking for the bonus a sales rep would get if they sell a particular amount of apples or bananas. In cases like this, you would use approximate match, because both values might not always match exactly and we want to get the closest value to what we have. For instance, from our table above, it says that if a rep sells 70 apples he gets 120 in bonus. But our rep sold 100 apples! If we had used false, it would return invalid. The IF argument is what does the trick. IF value is apple, the VLOOKUP function uses table two (apple). But anything other than apple, it would pull up data from table one (banana).
VLOOKUP + IFERROR function
To clear the #N/A error message that appears when VLOOKUP cannot find value requested,
we can use the IFERROR message to replace that with a friendlier error message. The IFERROR argument is:
=Iferror(value,value_if_error). So the function above would be
Summary of what we have learned so far and some points to note;
• VLOOKUP is case sensitive
• VLOOKUP looks to the right using the value on the left-most column of your table. Your table number and cell number are different.
• It can be used to lookup values on different worksheets and workbooks.
• If the first match contains duplicates, VLOOKUP would automatically match up the first one
• If the VLOOKUP function cannot find a match, it returns an #N/A error
• It can give you an exact match or an approximate match.
• It can be combined with other functions to increase search criteria.
- You must be logged in to reply to this topic.