## INDEX MATCH – Case sensitive

*Article last updated on March 25, 2018*

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

**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 function work so good together.

becomes

becomes

and returns 4 in cell F3.

### Alternative regular formula

### Download excel *.xlsx

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Filter unique distinct and duplicate values from a large data set in excel 2007

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]

Filter unique distinct values (case sensitive) [Excel UDF]

The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

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 […]

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 […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form