Contents
Problem
All Excel substitution functions from the category References and arrays (Lookup and Reference), such as VLOOKUP, HLOOKUP (HLOOKUP), MORE EXPOSED (MATCH) are case-insensitive (i.e., the difference between uppercase and lowercase) when looking up data. Thus, when using, for example, the VLOOKUP function to find the amount corresponding to the client with the code Smb3 in this table:
… in the end we get 38, not 56 – because the function does not see the difference between smb3 и Smb3 and displays the first encountered value from the table.
What to do if you need to find the value exactly, taking into account the coincidence of the case of characters? The answer is that you need a small array formula instead of VLOOKUP.
Council: if you haven’t really met array formulas before, then I highly recommend that you go and read this article to get a general idea about them. If the topic is familiar, then read on.
Solution
The formula we need looks like this:
=ИНДЕКС(B4:B9;СУММ(СОВПАД(A4:A9;D4)*СТРОКА(B4:B9))-СТРОКА(B3))
=INDEX(B4:B9,SUM(EXACT(A4:A9,D4)*ROW(B4:B9))-ROW(B3))
Let me remind you that at the end of entering this formula, you need to press the unusual Enterand the combination Ctrl + Shift + Enterto enter it not as a regular formula, but as an array formula. As you can see from the example, this formula finds the correct value 56, that is, in fact, distinguishes between lowercase and uppercase characters in writing code, unlike the classic VLOOKUP function.
How it works
To make it easier and clearer to explain exactly how this array formula works, it is best to use one of the tools for step-by-step debugging complex formulas in Microsoft Excel – the button Calculate Formula (Evaluate formula). You can find it on the tab. formula (Formulas) in group Formula Dependencies (Formula auditing).
In the window that opens, we will see the formula from the current cell and the button Calculate (Calculate), successively clicking on which, we will step by step consider the internal mechanism for calculating our formula:
So let’s go – the first stage. Function EXACT (EXACT) checks for an exact match between two text strings, case sensitive, and outputs TRUE or FALSE depending on the result. In our case, we slipped this function not two cells for comparison, as usual, but an array of cells (A4:A9), each of the cells of which will be compared in turn with cell D4, i.e. with our search value. After the first button press Calculate we will just see how this data is substituted into the formula:
After the second button press Calculate we will see the result of the comparison – the array {FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}. It can be seen that the exact match (TRUE) occurred only on the fifth element of the array:
Next stage. Function LINE (ROW) does a very simple, in fact, thing – it gives the line number for the current cell. Here we give it as an argument not one cell, but an array (B4:B9), so we get a set of row numbers for each array cell, i.e. {4;5;6;7;8;9}:
Then these two arrays are multiplied in pairs with each other, giving us the output array {0;0;0;0;8;0}, because FALSE (FALSE) in the understanding of Excel is equivalent to zero, and TRUE (TRUE) – unit:
To get the serial number of the line with the data we need inside tables – subtract from the line number on the sheet (8) the line number of the beginning of the table, which is determined by the function ROW(B3):
Thus, we end up with the number 5 – the row number in our table, where there is an exact match with the desired value from D4, case sensitive. It remains to extract data from the desired column cell by the calculated row number. This is what the function does INDEX (INDEX), whose first argument is an array of our values (B4:B9), and the second is the row number in this array (5), from which we want to extract the value we need:
- What are array formulas, how to enter and edit them, why are they needed.
- Using the VLOOKUP function to substitute values
- Improved version of the VLOOKUP function (VLOOKUP2)
- Dynamic selection from a list using the INDEX and MATCH functions