## 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 value in cell F2 (case sensitive).

**Array formula in E5:**

**Array formula in F5:**

### How to enter an array formula

Double click on cell E5, copy above formula and paste to cell E5. Press and hold CTRL + SHIFT, then press Enter once. Release all keys.

The formula bar displays the formula enclosed with curly brackets, they appear automatically. Don't enter these values yourself.

Repeat above steps to enter array formula in cell F5. Then copy cell range E5:F5 and paste to cells below as far as needed.

### Explaining formula in cell E5

*Step 1 - Check if values are an exact match to lookup value*

EXACT($B$3:$B$9, $F$2)

becomes

EXACT({"John";"john";"joHn";"john";"John";"john";"John"},"John")

and returns {TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE}

The picture below shows the array in column E. TRUE means that the value in column B on the same row is identical to the lookup value.

*Step 2 - Convert boolean array to row numbers if TRUE*

IF({TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE},MATCH(ROW($B$3:$B$9), ROW($B$3:$B$9)), "")

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE},{1;2;3;4;5;6;7}, "")

and returns {1;"";"";"";5;"";7}.

The picture below displays the array in column E. Now we know which rows the identical values have.

*Step 3 - Extract the k-th smallest value from array*

SMALL(IF(EXACT($B$3:$B$9, $F$2),MATCH(ROW($B$3:$B$9), ROW($B$3:$B$9)), ""), ROWS($A$1:A1))

becomes

SMALL( {1;"";"";"";5;"";7}, ROWS($A$1:A1))

becomes

SMALL( {1;"";"";"";5;"";7}, 1)

and returns 1. Note that ROWS($A$1:A1) change when you copy the cell and paste to cells below. This makes the formula show all identical values.

*Step 4 - Get value from cell range*

INDEX($B$3:$B$9, SMALL(IF(EXACT($B$3:$B$9, $I$2), MATCH(ROW($B$3:$B$9), ROW($B$3:$B$9)), ""), ROWS($A$1:A1)))

becomes

INDEX($B$3:$B$9, 1)

becomes

INDEX({"John";"john";"joHn";"john";"John";"john";"John"}, 1)

and returns John in cell E5.

### Download excel *.xlsx file

Case sensitive vlookup and returning multiple values

### Functions in this article:

**EXACT(**text1, text2**)**

Checks whether two text strings are exactly the same and returns TRUE or FALSE. EXACT is case sensitive.

**IF(**logical_test,[value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

Filter unique distinct and duplicate values from a large data set

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

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

The EXACT function allows you to check if two values are precisely the same, it returns TRUE or FALSE. The […]

How to extract a case sensitive unique list from a column

My definition of unique values are values that exist only once in a cell range. The image below shows you […]

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