Hi, friend! Last week, I talked about the VLOOKUP and IF functions as a complement to the YouTube video I made on creating an order form for free without using a website. However, there was still a lot of confusion about how VLOOKUP works, so I decided to do a whole video this week to that effect. In this week's blog, I will explain the 2 different scenarios where VLOOKUP can be used. If you have heard this song before, then feel free to log out now after liking and sharing this article. Next week will be your week, in Jesus' name!
Here is this week's YouTube video:
VLOOKUP to look up an exact match
This week's workbook can be found here.
The VLOOKUP function takes 4 arguments: search_key, range, index and [is_sorted] in Google Sheets or [range_lookup] in Excel; with the first 3 being compulsory.
search_key is the value you are looking up from another table or range
range is the table or range within which you are searching for the search_key. This range can be in the same sheet, or a separate sheet entirely. The only caveat is that the column that contains the search_key(s) must be the left-most column.
index is the number of the column in the range/table which contains the value that corresponds to or is adjacent to the search_key exact or approximate match.
[is_sorted] or [range_lookup] is an optional argument. We know it is optional because it is surrounded by [square brackets]. This argument defaults to look for an EXACT match of the search_key in the desired range/table and returns the corresponding value or the value adjacent to the search_key in the range. Therefore, we can ignore this argument when the search_key is an EXACT match to the item in the left-most column of the table, or enter FALSE in the argument.
For example, looking at the image below, if we want to find the prices of the fruits in table A by pulling them from the price list in table B, we can use VLOOKUP. We would type our formula in the first cell in table A below Amount as follows:
=VLOOKUP(cell_containing_Orange, range_of_fruits_and_their_prices_from_Mangoes_to_1200, column_number_of_price_column)search_key = cell_containing_Orange in Table A,
range = range_of_fruits_and_their_prices_from_Mangoes_to_1200 in Table B,
index = column_number_of_price_column which is 2, because the price column is the second column in table B
We ignore the fourth argument because the Items in both tables are an Exact match and Excel/ Google Sheets defaults to consider EXACT matches.
Video illustration from this week:
VLOOKUP with an APPROXIMATE match
However, if the items in the left-most column of the range are not EXACT matches to the search_key(s), then we need to use the [is_sorted] or [range_lookup] argument to tell Excel or Google Sheets to look for APPROXIMATE matches to the search_key(s) and return the corresponding values in index column 2. To use this argument for approximate matches, we enter TRUE in Google Sheets or Excel.
For example, in this week's video, we use VLOOKUP to pull grades for student scores, but the values in the grade book, do not match the student scores EXACTLY. Therefore, we must include TRUE in the [is_sorted] or [range_lookup] argument to find the score in our desired range that is closest to the student score or the next smallest value. Here is the illustration:
Conclusion
I hope that between this week's and last week's posts, you are now an expert in using the VLOOKUP function. It is an old function, but it is still essential.
Have a great week!
Comments