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 press with left mouse button 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.
Get 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
Case sensitive category
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]
Excel categories
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.