Explanation
This example shows how to use the VLOOKUP function to calculate the total shipping cost for an item in one formula, where the cost per kilogram (kg) varies according to weight. This requires an “approximate match” since in most cases the actual weight will not appear in the shipping cost table. For convenience, the range E5:F9 is named cost_table . This is an “approximate-match” lookup problem because it is not likely that a given weight will be found in the cost table. As a result, the formula needs to match the largest weight in the table that is less than or equal to the given weight . Finally, the result of the lookup operation should multiply the cost per kilogram returned by VLOOKUP by the weight in column B to get a final cost.
VLOOKUP function
VLOOKUP is an Excel function to get data from a table organized vertically . Lookup values must appear in the first column of the table provided to VLOOKUP, and the information to retrieve is specified by column number. If you are new to VLOOKUP, see:
- How to use VLOOKUP - overview with examples and video links
VLOOKUP solution
In the worksheet shown, the formula in cell C5 is:
=VLOOKUP(B5,cost_table,2,TRUE)*B5
where cost_table (E5:F9) is a named range . VLOOKUP requires lookup values to be in the first column of the lookup table. To retrieve the correct cost per kilogram for a given weight, VLOOKUP is configured like this:
- The lookup_value comes from cell B5
- The table_array is the named range cost_table (E5:F9)
- The col_index_num is 2 since the cost appears in the second column of the table
- The range_lookup argument is set to TRUE = approximate match
With a score provided as a lookup value, VLOOKUP will scan the first column of the table. If it finds an exact match, it will return the cost in that row. If VLOOKUP does not find an exact match, it will continue scanning until it finds a value greater than the lookup value, then it will “step back”, and return the cost in the previous row. As the formula is copied down column C, the VLOOKUP function looks up each weight in column B and returns the correct cost per kilogram (kg). Finally, the result from VLOOKUP is multiplied by the original weight in column B to get a total shipping cost.
Notes: (1) In approximate match mode, VLOOKUP assumes the table is sorted by the values in the first column. This means the cost table must be sorted in ascending order by weight. (2) As an alternative, you could add a helper column that contains only the VLOOKUP part of the formula to calculate the cost per kilogram, then perform the multiplication separately.
Named range optional
The named range in this example is optional and used for convenience only because it makes the formula easier to read and means that the cost table does not need to be locked when the formula is copied down column C. To avoid using a named range, use an absolute reference like this:
=VLOOKUP(B5,$E$5:$F$9,2,TRUE)*B5
VLOOKUP match modes
VLOOKUP has two match modes: exact match and approximate match, controlled by an optional fourth argument called range_lookup . When range_lookup is omitted, it defaults to TRUE and VLOOKUP performs an approximate match. This means we could leave out the range_lookup and get the same result with this formula:
=VLOOKUP(B5,cost_table,2)*B5
However, I recommend that you always provide a value for range_lookup because it makes you consider the behavior you want. In other words, giving a value for range_lookup acts as a reminder to you and others of the intended behavior.
Notes: If the weight is less than the first entry in the table, VLOOKUP will return the #N/A error. In the example shown, we use zero in cell E5 to make sure this does not occur.
Adding a minimum cost
To modify the formula to return a minimum cost of $15, regardless of weight, you can nest the original formula inside the MAX function like so:
=MAX(VLOOKUP(B5,cost_table,2,TRUE)*B5,15)
After the original formula runs and returns a calculated total cost, the MAX function will return whichever is greater - the result of the formula or 15.
Explanation
In this example, the goal is to perform a case-sensitive lookup on Color with VLOOKUP. In other words, a lookup value of “RED” must return a different result from a lookup value of “Red”. This presents several challenges. First, Excel is not case-sensitive by default, and there is no built-in setting to make VLOOKUP case-sensitive. For example, if we try a standard VLOOKUP formula in exact match mode, we get the wrong result:
=VLOOKUP("RED",data,3,0) // returns 17
VLOOKUP matches “Red” in row 3, and returns 17. The correct result is 10.
The second challenge is the table itself. Unlike XLOOKUP or INDEX and MATCH, VLOOKUP requires the entire table to be provided in the table_array argument. Normally, this is not a problem. However, to enable a case-sensitive VLOOKUP, we can’t use the existing table as-is, and this means we need to take special steps to assemble a table that will work for this problem.
The overall process looks like this:
- Use the EXACT function to check the Color column for the lookup value.
- Join the results from EXACT to the Qty column with the CHOOSE function.
- Provide the resulting array to VLOOKUP as the table_array argument.
- Configure VLOOKUP to look for TRUE in the new table.
The result is a case-sensitive lookup with VLOOKUP. Read on for a complete explanation.
Background reading
This article assumes you are familiar with the VLOOKUP function and Excel Tables. If not, see:
- Excel Tables - introduction and overview
- VLOOKUP function - overview with examples
- EXACT function - overview
EXACT function
The EXACT function is designed to perform a case-sensitive comparison of two text values. If the two values match exactly, EXACT returns TRUE. If not, EXACT returns FALSE. The twist in this case is that we need to check every value in the Color column against the value in F5. Fortunately, the EXACT function will do this.
Working from the inside out, we set up the EXACT function like this:
=EXACT(data[Color],F5)
Since there are 12 values in the Color column, the EXACT function will return a vertical array with 12 TRUE and FALSE results like this:
{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Notice the position of TRUE (5) corresponds to row 5 in the table, where Color is “RED”. EXACT returns FALSE for every other value, including “Red” in row 3. This gives us an array we can use in the next step.
CHOOSE function
We now have an array of TRUE and FALSE values that will function as a key to which row(s) in the table match “RED”. The problem is that the array is not actually part of the table, and VLOOKUP needs an entire table as the table_array argument. In addition, the first column in the table must contain lookup values. What we need is a new table, that combines the result from EXACT with the values in the Qty column.
Enter the CHOOSE function . Normally, the CHOOSE function is used to select a value by numeric position. For example, to get the second value from a list of three values, you could use CHOOSE like this:
=CHOOSE(2,value1,value2,value3) // returns value2
CHOOSE is flexible when it comes to values, which can be any mix of constants, cell references, arrays, or ranges. In this case, we give CHOOSE the array created by the EXACT function as value1 and the Qty column in the table as value2 . Then, for index_num , we provide the array constant {1,2} like this:
=CHOOSE({1,2},EXACT(data[Color],F5),data[Qty])
The array constant is the tricky part. By using the array {1,2} we are requesting value1 and value2 at the same time. As a result, the array from EXACT and data[Qty] are “glued” together and returned as a single array. This array only exists in memory, but it looks like this:
As you can see, the array has 2-columns. We now have a table we can use in VLOOKUP.
Note: whenever you see =CHOOSE({1,2} you should think “2 things are being joined together”.
VLOOKUP function
Next, we need to connect the code above to the VLOOKUP function . We know the first column in the array we created contains TRUE or FALSE values, so we start with a lookup value of TRUE:
=VLOOKUP(TRUE,
This may seem strange, but remember that the original color values are gone (replaced by TRUE and FALSE) and so we need to look for TRUE and not “RED”. Next, for the table_array argument, we add the code that creates our custom table:
=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(data[Color],F5),data[Qty])
The array created by CHOOSE is returned directly to the VLOOKUP function as the table_array argument. Because the values we want to retrieve are in the second column, we set col_index_num to 2. Then we set the range_lookup argument to zero or FALSE, to enable an exact match. The final formula in G5 is:
=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(data[Color],F5),data[Qty]),2,0)
VLOOKUP matches the text “RED” in row 5 of the table and returns 10 as a final result.
Note: This is an array formula and must be entered with control + shift + enter, except in Excel 365 or Excel 2021.
