Explanation
In this example, the goal is to perform a case-sensitive lookup on Color with VLOOKUP.
This presents several challenges.
First, Excel isnotcase-sensitive by default, and there is no built-in setting to make VLOOKUP case-sensitive.
The correct result is 10.
The second challenge is the table itself.
Unlike XLOOKUP or INDEX and MATCH, VLOOKUPrequiresthe entire table to be provided in thetable_arrayargument.
Normally, this is not a 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 the two values match exactly, EXACT returns TRUE.
If not, EXACT returns FALSE.
Fortunately, the EXACT function will do this.
EXACT returns FALSE for every other value, including “Red” in row 3.
This gives us an array we can use in the next step.
In addition, the first column in the table must contain lookup values.
What we need is anewtable, that combines the result from EXACT with the values in the Qty column.
Normally, the CHOOSE function is used to select a value by numeric position.
We now have a table we can use in VLOOKUP.
VLOOKUP function
Next, we need to connect the code above to theVLOOKUP function.
Because the values we want to retrieve are in the second column, we setcol_index_numto 2.
Then we set therange_lookupargument to zero or FALSE, to enable an exact match.
The values provided to CHOOSE can include references.
