where “data” is an Excel Table in the range B5:D16. In this configuration, VLOOKUP matches the text “RED” in row 5 of the table and returns 10 from the Qty column in the same row. This is an array formula and must be entered with control + shift + enter, except in Excel 365 or Excel 2021. Note: With XLOOKUP, or INDEX and MATCH, the approach is the same but the formulas are more straightforward: XLOOKUP example, INDEX and MATCH example. 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: 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: 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: 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 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: 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: 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: 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
