The thing that tends to mislead Excel users is the description that Microsoft used for these options. It is a boolean argument, meaning you can pass it a value of TRUE or FALSE, or any other representation of TRUE or FALSE. The fourth argument of the VLOOKUP function is officially named: range_lookup. The Truth about the VLOOKUP Fourth Argument Now that we have covered the basics, it is time to explore the VLOOKUP’s fourth argument. If the value is not the same, the function will not match it, and you’ll get an error, as shown in the screenshot below. No leading spaces, no trailing spaces, no extra abbreviations or characters. “ABC Company” would not match “ABC Company, Inc.”, “ABC Co”, or “ABC Company “. Except for case (upper and lower), the two values must match exactly. It is important to note that the lookup value, the text string “ABC Company” must be found in the lookup range.
Find a value (the match) and compute the result (the return). And that my friend is the basic idea of the VLOOKUP function. This function is entered in C8 in the screenshot below.Īs you can see, the ID AC100 was successfully returned to the formula cell C8.
FALSE means we are not performing a range lookup. 2 is the column that has the value we wish to return. Assuming the customer name was entered in C7, and the customers were stored in a Table named Table1, then the following function would do the trick: I would use a VLOOKUP function, and I would ask it to find “ABC Company” in the Customer Table, and return the ID. I would like to find a specific customer name “ABC Company” in a list of customers, and if found, I would like Excel to return the customer id which is found in the next column. So, based on which lookup function you select, and which function argument values you enter, Excel knows what to return once it finds its match. Other lookup functions, such as the VLOOKUP function, tell Excel to return a related value. Some lookup functions, such as the MATCH function, tell Excel to return the position number. That is, what value the function should return to the cell. Step two, the return, is the function’s result. You are asking Excel to find the lookup value in the lookup range. You tell Excel the value to find, such as “ABC Company” and you tell Excel where to look, such as in a range of cells. In the first step, the match, Excel must find the matching value. I’ll call step one the match, and step two the return. That is the basic idea, but the application of lookup functions are numerous and the implementations can become quite sophisticated and powerful.įor this post, I’d like to split the tasks that a lookup function performs into two steps. For example, we could ask Excel to find “ABC Company” in a list of customer names. The basic idea of an Excel lookup function is to look for a value in a list. Since the VLOOKUP function is probably the most used and most familiar lookup function, we’ll use it as we explore these ideas. To simplify this post, we’ll use just one as the example. The built-in Excel lookup functions, such as VLOOKUP, HLOOKUP, and MATCH, work with similar lookup logic. *** UPDATED BLOG POST: FUZZY MATCH IS AVAILABLE IN POWER QUERY *** Understanding Built-In Lookup Functions 
This post discusses the details of these ideas, and demonstrates how to perform a fuzzy lookup in Excel 2010 and later. Both of these are quite different from an approximate match or a fuzzy lookup. However, the VLOOKUP function, similar to Excel’s other lookup functions such as HLOOKUP and MATCH, is built to perform an exact match or a range lookup. The VLOOKUP function alone has saved countless hours in my recurring use workbooks.
When implemented in the right way for special projects or in recurring use workbooks, they are able to save a ton of time. The built-in Excel lookup functions, such as VLOOKUP, are amazing. This post explores Excel’s lookup functions, approximate matches, fuzzy lookups, and exact matches.
0 kommentar(er)
