CASE SENSITIVE LOOKUP IN MICROSOFT EXCEL
- As discussed last week in my last blog, I’ll be writing on how to perform case sensitive lookup in excel with index, match and exact function.
- In my last blog, we saw how to use index and match functions to perform lookups in excel.
- The dummy data in my last blog will be used to perform this operation.
- In the data, we have 2 Mary’s (Mary Bajisma and mary Peter). We looked up for the score of Mary Bajisma last week.
- In today’s blog, I’ll be writing on how to lookup for the score of mary peter without returning the same value we got from Mary Bajisma.
- To do the above, we have to introduce a function called the “exact function”.
The Microsoft Excel EXACT function compares two strings and returns TRUE if both values are exactly the same. Otherwise, it will return FALSE.
- Let’s start by first using the standard index and match function like we used last week to lookup the score of mary peter. To do this, write the formula below:
=INDEX(C2:C7, MATCH(H4,A2:A7,0))
#C2:C7 is the lookup column array which is the Score column
#H4 is the lookup value which is mary
#A2:A7 is the array of the First Name column (where our lookup value can be located i.e mary)
#0 stands for exact match
The above formula will return a score of 87 which is not the correct score as depicted above. This score is the score for Mary Bajisma, but, we are meant to get 92.
- We are now going to use the exact function with the index and match function to get the correct answer. To do this, the formula below will be written:
=INDEX(C2:C7,MATCH(TRUE,EXACT(H4,A2:A7),0))
#C2:C7 is the lookup column array which is the Score column
#The match function in this case has 3 arguments which encompasses the exact function while the exact function has two arguments:
#The first argument is TRUE which means the formula should be executed only when an exact match is found. If you use FALSE, it will execute the formula when the first non-exact match is found.
#The second argument which is the exact function has two arguments which are text1 and text2. The text 1 and text 2 represents the two strings to be compared. One of the arguments can be an array. In our case, text1 is H4 which is the lookup value (mary), while text2 is A2:A7 which is the array of the First Name column (where our lookup value can be located i.e mary).
#In other words, the exact function will iterate through each entry in the First Name column and compare it with the lookup value (mary) to see if there is an exact match or not and will return true when an exact match is found.
#0 which is the last argument represents exact match.
N.B: For the above formula to work accurately, you have to press CTRL+SHIFT+ENTER after typing the formula instead of just the normal ENTER. This is done because we are performing a calculation with an array formula.
- After doing this, the formula will be wrapped in curly braces as shown in the picture above.
- With this formula, we will get the score of mary peter which is 92.
If while learning, you feel the need to ask questions for clarity, you can send me a mail @marybajisma123@gmail.com or whatsapp 09021833246.
