In our scenario (see video below) we want to perform a lookup on some product codes. Our product codes have variants where some have upper and some lower case characters. Generally Excel ignores case so PRO123 is seen as the same as Pro123. A normal VLOOKUP is blind to case so would pick up the first variant it found of the product code and not necessarily the one you want!
To get around this we can use the EXACT function. The EXACT function allows you to compare two text values and will return TRUE if they are identical and FALSE if they are not. The EXACT function is case sensitive. The arguments in the EXACT function are text1 and text2.
Normally if we are doing a lookup we are looking for a value in a table or more precisely in the left most column of a table. So we would need to compare text1 with a range or array of possible values. We are going to need to use an array formula to solve this.
Instead of EXACT(E1, A2) – comparing the value in E1 with A2 (following the video example)
We need to say EXACT(E1,A2:A5): look for E1 in the range A2 to A5.
Now this wouldn’t normally work as the EXACT function is set up to compare one text value with another. But we can create an array formula by confirming with CTRL SHIFT ENTER rather than just ENTER. This creates the following formula – note the curly brackets {=EXACT(E1,A2:A5)}.
Once we have done this it is not exactly clear what we have achieved as the formula will simply return TRUE or FALSE. But if we put the formula back into edit mode and press the F9 function key on our keyboard we can see that Excel is actually producing a TRUE or FALSE for every value in the range we have specified in the text2 argument. Now in our example ={FALSE;TRUE,FALSE,FALSE} indicates that there is an EXACT match with the second value in the range.
To utilise the position of the TRUE value we can use the MATCH function. The MATCH function helpfully finds the position of a value within a one dimensional range (normally a row or a column) but we can also use it to find the position of TRUE within our array. Match has three arguments: lookup value, lookup array and match type. Our lookup value is TRUE, our array is created by our EXACT function as above and our match type is exact (0). We have to use CTRL SHIFT and ENTER to confirm the array formula.
{=MATCH(TRUE, EXACT(E1,A2:A5),0)}
Now we have the row position of product we can use the INDEX function to pick a corresponding value in the same row. In our example we need to return a sales value in column B. INDEX allows you to lookup a vertical and/or horizontal position within an array. We only need a vertical position. The arguments we will use are array (B2:B5) and row number (created by our MATCH function). Don’t forget to use CTRL SHIFT and ENTER to confirm.
{=INDEX(B2:B5, MATCH(TRUE, EXACT(E1,A2:A5),0)}