INDEX MATCH – Case sensitive
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B using the value in cell F2, also considering letter casing, then return the corresponding value from column C.
Table of Contents
1. How to do a case sensitive INDEX MATCH
1.1 How to enter an array formula
The formula above is an array formula. To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
If you prefer a regular formula, read "Alternative regular formula" below in this article.
1.2 Explaining formula in cell F3
Step 1 - Compare lookup value with lookup column
The EXACT function allows you to compare values, if they are exactly the same the EXACT function returns TRUE. Note, the function is case-sensitive.
EXACT(F2,B3:B8)
becomes
EXACT("aAA",{"aaa"; "aaA"; "aAA"; "AAA"; "AAa"; "Aaa"})
and returns
{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.
Step 2 - Identify the relative position of value TRUE in the array
The MATCH function finds a specific value in an array or cell range and returns its location, a number representing the position.
MATCH(TRUE,EXACT(F2,B3:B8),0)
becomes
MATCH(TRUE, {FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, 0)
and returns 3. TRUE is in the third position in the array.
Step 3 - Return corresponding value from column C
The INDEX function returns a value from an array or cell range based on the location. That is why the INDEX and MATCH functions work so well together.
INDEX(C3:C8,MATCH(TRUE,EXACT(F2,B3:B8),0))
becomes
INDEX(C3:C8,3)
becomes
INDEX({6;5;4;3;2;1},3)
and returns 4 in cell F3.
2. Alternative regular formula
Formula in cell F2:
3. How to do a case sensitive partial match using INDEX and MATCH functions
Formula in cell F3:
3.1 Explaining formula in cell F3
Step 1 - Perform a case sensitive search (partial match)
The FIND function returns a number representing the position of a specific substring in another string, reading left to right. Note, the FIND function is case-sensitive.
FIND(F2, B3:B8)
becomes
FIND("E", {"Cat"; "Horse"; "Snake"; "Tiger"; "Elephant"; "Mouse"})
and returns
{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!}
Notice the error values in the array above, they appear if the substring is not found for each value in cell range B3:B8.
Step 2 - Identify numbers
The ISNUMBER function checks if a value is a number, it returns either TRUE or FALSE.
ISNUMBER(FIND(F2, B3:B8))
becomes
ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!})
and returns
{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.
Step 3 - Find the position of the first number in the array
The MATCH function returns a number representing the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(TRUE, ISNUMBER(FIND(F2, B3:B8)), 0)
becomes
MATCH(TRUE, {FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}, 0)
and returns 5. TRUE is first found in position five in the array above.
Step 4 - Get corresponding value from the same row
The INDEX function returns a value from a given cell range based on a row and column number (optional).
INDEX(array, [row_num], [column_num], [area_num])
INDEX(C3:C8, MATCH(TRUE, ISNUMBER(FIND(F2, B3:B8)), 0))
becomes
INDEX(C3:C8, 5)
becomes
INDEX({6;5;4;3;2;1}, 5)
and returns 2.
4. How to do a partial case sensitive match using two conditions in two columns
Formula in cell F3:
4.1 Explaining formula in cell F3
Step 1 - Perform a case sensitive search (partial match)
The FIND function returns a number representing the position of a specific substring in another string, reading left to right. Note, the FIND function is case-sensitive.
FIND(find_text,within_text, [start_num])
FIND(G4, B5:B10)
becomes
FIND("W", {"Brown"; "YeLLow"; "Pink"; "BLuE"; "BroWn"; "Black"})
and returns
{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!}.
Step 2 - Perform a second case sensitive search (partial match)
FIND(G5, C5:C10)
becomes
FIND("M",{"small";"SMALL";"LaRgE";"SmAlL";"sMall";"small"})
and returns
{#VALUE!; 2; #VALUE!; #VALUE!; 2; #VALUE!}.
Step 3 - Multiply arrays
The formula returns a value if both substrings are found on the same row, to do that we need to apply AND logic.
The asterisk character lets you multiply values row by row, if both values are numbers the result is a number.
#VALUE! * #VALUE! = #VALUE!
#VALUE! * 2 = #VALUE!
2 * #VALUE! = #VALUE!
2*2 = 4
FIND(G4, B5:B10)*FIND(G5, C5:C10)
becomes
{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!} * {#VALUE!; 2; #VALUE!; #VALUE!; 2; #VALUE!}
and returns
{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 8; #VALUE!}.
Step 4 - Identify numbers
The ISNUMBER function checks if a value is a number, it returns either TRUE or FALSE.
ISNUMBER(FIND(G4, B5:B10)*FIND(G5, C5:C10))
becomes
ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; 8; #VALUE!})
and returns {FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.
Step 5 - Find the position of the first number in the array
The MATCH function returns a number representing the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(TRUE, ISNUMBER(FIND(G4, B5:B10)*FIND(G5, C5:C10)), 0)
becomes
MATCH(TRUE, {FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}, 0)
and returns 5.
Step 6 - Get corresponding value on the same row
The INDEX function returns a value from a given cell range based on a row and column number (optional).
INDEX(array, [row_num], [column_num], [area_num])
INDEX(D5:D10, MATCH(TRUE, ISNUMBER(FIND(G4, B5:B10)*FIND(G5, C5:C10)), 0))
becomes
INDEX(D5:D10, 5)
becomes
INDEX({1;2;3;4;5;6}, 5)
and returns 5.
5. Get Excel *.xlsx
INDEX MATCH Case sensitive.xlsx
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Filter unique words from a range [UDF]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
Count unique distinct values based on a condition
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
Compare two columns and highlight differences
This article demonstrates a conditional formatting formula that will highlight the differences between two columns. The image above shows two […]
If cell equals value from list
This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table […]
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
How to highlight dates based on day of week
The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]
This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]
INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]
2 Responses to “INDEX MATCH – Case sensitive”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
The first formula didn't seem to work.
However, =INDEX(C3:C8, MATCH(TRUE, INDEX(EXACT(F2, B3:B8), ), 0))
worked for me.
Thank you :)
Sunil,
The first formula is an array formula. You probably didn't press CTRL + SHIFT + ENTER to create an array formula.