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.
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, skip to "Alternative regular formula", below in this article.
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.
becomes
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.
becomes
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 function work so good together.
becomes
becomes
and returns 4 in cell F3.
Alternative regular formula
Download excel *.xlsx
INDEX MATCH Case sensitive.xlsx
Weekly Blog EMAIL
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
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Case sensitive lookup and return multiple values
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
Lookup multiple values across columns and return a single value
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]
INDEX MATCH – multiple results
The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]
INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]
INDEX and MATCH – multiple criteria and multiple results
The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]
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.