Case Sensitive Vlookup in Excel; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value
Related Links:
1. Excel VLOOKP Function, with examples.
2. Left Lookup with VLookup Excel function.
3. Left Lookup, with Index, Match & Offset Excel functions.
4. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value.
Case Sensitive Vlookup
VLOOKUP function searches for a value (lookup value) in the first column of a table array and returns the corresponding value in the same row from another column in the table array. The lookup value which the function searches, is not case-sensitive. There are several methods to make the lookup value case-sensitive, as shown below. Many of them use the excel EXACT function.
EXACT function Syntax: EXACT(text1,text2). Text1 is the first text string. Text2 is the second text string. EXACT compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. It is case-sensitive.
————————————————————————————————————————
Case Sensitive Vlookup – when Lookup Value of only ONE CASE is present
Example 1:
=IF(EXACT(D2, VLOOKUP(D2, $A$2:$B$10, 1, FALSE))=TRUE,VLOOKUP(D2, $A$2:$B$10, 2,FALSE), “Not Found”) [Formula]
Returns 56, refer Table 1a. Formula searches for lookup value “JACK” in column A, checks whether the value found matches in CASE, and if it does then returns the corresponding value from column B.
This formula might not work if lookup value in different cases is present. If the list contains “JACK” and “jack” it would find the first one and only test that.
=IF(EXACT(D3, VLOOKUP(D3, $A$2:$B$10, 1,FALSE))=TRUE, VLOOKUP(D3,$A$2:$B$10, 2,FALSE), “Not Found”) [Formula]
Returns “Not Found”, refer Table 1b. Formula searches for lookup value “jack” in column A and matches case, but finds the first value “JACK” whose case does not match, hence it returns “Not Found”.
————————————————————————————————————————-
Case Sensitive Vlookup – Find the 1st, 2nd, 3rd or nth OCCURRENCE of a Value
Example 2:
=INDEX($B$2:$B$10, SMALL(IF(EXACT(D2, $A$2:$A$10), ROW($A$2:$A$10)- ROW($A$2)+1), 1)) [Formula]
Enter as an array formula (CTRL+SHIFT+ENTER). Returns the value 59, refer Table 2. The formula does a case sensitive search for the lookup value (“jack” in cell D2) in column A and returns the corresponding value in column B, in the table array “A2:B10”. It returns the corresponding value related to the first occurrence of the lookup value. It effectively does a case sensitive Vlookup of: =VLOOKUP(D2, A2:B10,2, FALSE).
Step 1: Determine Row Number of the First Record
Formula Part =-ROW($A$2)+1 indicates the number (in -ive) of rows above the first record, wherein =ROW($A$2) is the row number of the first record in the table array. This returns the value: -2 +1 = -1. NOTE: If the table array is (“A3:B11”), then change this formula to =-ROW($A$3)+1.
Step 2: Determine Row Number of Lookup Value
Formula Part =IF(EXACT(D2, $A$2:$A$10), ROW($A$2:$A$10)) matches “jack” (case sensitive) in column A and evaluates to: {FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,9,FALSE}, wherein the number 9 indicates the worksheet row number containing “jack”. Formula Part =IF(EXACT(D2, $A$2:$A$10), ROW($A$2:$A$10)- ROW($A$2)+1) evaluates to: {FALSE,FALSE, FALSE,FALSE, FALSE,FALSE, FALSE, 8,FALSE}, wherein 8 indicates the relative row number in the table array containing “jack”.
Step 3: Determine Position of smallest Row Number containing Lookup Value
Formula Part =SMALL(IF(EXACT(D2, $A$2:$A$10), ROW($A$2:$A$10)- ROW($A$2)+1), 1) returns the smallest position (indicated by the last number in the formula, which is 1) of the relative row numbers in the table array which contain the lookup value of “jack”. Computing the smallest position effectively means the first occurrence of the Lookup Value. In this example, it is position 8 (ie. relative row number in the table array).
Step 4: Using INDEX Function to determine Corresponding Value of the Lookup Value
The INDEX function, INDEX(array, row_num, column_num), accounts for the array as “$B$2:$B$8”, and row_num in the table array as 8, and returns the intersection (column B, row number 8) value of 59.
To return the corresponding value related to the 2nd, 3rd or nth occurrence of the lookup value:
We have explained above in Step 3, that the first occurrence of the lookup value is returned by determining the smallest position which is indicated by 1 (ie. the last number) in the formula. By changing the last number in the above formula from 1 to say 2, you can get the second occurrence of the lookup value (in case there are multiple values “jack” in column A): =INDEX($B$2:$B$10, SMALL(IF(EXACT(D2, $A$2:$A$10), ROW($A$2:$A$10)- ROW($A$2)+1), 2)). Changing the last number to 3 will give the third occurrence of the lookup value. You can in this manner toggle this last number to return the nth occurrence of the lookup value.
————————————————————————————————————————–
Vlookup (Non-Case Sensitive) – Find the 1st, 2nd, 3rd or nth OCCURRENCE of a Value
In case there are multiple occurrences of the lookup value in different cases viz. JACK, Jack & jack, and you do not want Vlookup to be case sensitive while finding the nth occurrence of the lookup value, refer Example 3 below.
Example 3:
=INDEX($B$2:$B$10, SMALL(IF($D$2=$A$2:$A$10, ROW($A$2:$A$10)- ROW($A$2)+1), 2)) [Formula]
Enter as an array formula (CTRL+SHIFT+ENTER). Returns the value 32, refer Table 3. The formula does a NON-case sensitive search for the lookup value (“JaCK” in cell D2) in column A and returns the corresponding value in column B, in the table array “A2:B10”. It returns the corresponding value related to the second occurrence of the lookup value.
By changing the last number in the above formula from 2 to say 1, you can get the first occurrence of the lookup value: =INDEX($B$2:$B$10, SMALL(IF($D$2=$A$2:$A$10, ROW($A$2:$A$10)- ROW($A$2)+1), 1)). Changing the last number to 3 will give the third occurrence of the lookup value. You can in this manner toggle this last number to return the nth occurrence of the lookup value.
Formula explanation is similar to Example 2.
————————————————————————————————————————–
Case Sensitive Vlookup – Find the FIRST OCCURRENCE of a Value
Note: The normal Vlookup excel formula searches the first occurrence of the Lookup Value.
Example 4:
=INDEX($B$2:$B$10, MATCH(TRUE, INDEX(EXACT($D$2, $A$2:$A$10), 0), 0)) [Formula]
Returns the value 59, refer Table 4. The formula does a case sensitive search for the lookup value (“jack” in cell D2) in column A and returns the corresponding value in column B, in the table array “A2:B10”. It effectively does a case sensitive Vlookup of: =VLOOKUP(D2, A2:B10, 2,FALSE).
INDEX function Syntax: INDEX(array, row_num, column_num). It returns the value of an element in a table or an array selected by the row and column number indexes. If both the row_num and column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num. If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively.
Formula Part =EXACT($D$2,$A$2:$A$10) evaluates to: {FALSE,FALSE, FALSE,FALSE, FALSE,FALSE,FALSE, TRUE,FALSE}. Combined with INDEX furnction (with row_num set to 0 as explained above), it returns the array.
Formula Part =MATCH(TRUE, INDEX(EXACT($D$2, $A$2:$A$10), 0), 0) returns the value 8, which is the relative position of “jack” in the above array. MATCH function looks up the value “TRUE” in the above array and returns its relative position of 8. MATCH function Syntax: MATCH(lookup_value, lookup_array, match_type). If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
The INDEX function, INDEX(array, row_num, column_num), accounts for the array as “$B$2:$B$8”, and row_num in the table array as 8, and returns the intersection (column B, row number 8) value of 59.
Example 5:
=INDEX($B$2:$B$10, MATCH(TRUE, EXACT($D$2, $A$2:$A$10), 0)) [Formula]
Enter as an array formula (CTRL+SHIFT+ENTER). Returns the value 59, refer Table 5. The formula does a case sensitive search for the lookup value (“jack” in cell D2) in column A and returns the corresponding value in column B, in the table array “A2:B10”. It effectively does a case sensitive Vlookup of: =VLOOKUP(D2, A2:B10,2, FALSE).
This formula is similar to the above except that it does not combine the INDEX function with EXACT, and therefore is entered as an array formula to return an array. This formula includes the EXACT function: EXACT($D$2,$A$2:$A$10); whereas the previous formula had combined INDEX & EXACT: INDEX(EXACT($D$2, $A$2:$A$10), 0).
————————————————————————————————————————–
Case Sensitive Vlookup – Find the LAST OCCURRENCE of a Value
Example 6:
=LOOKUP(9.99999999999999E+307, FIND($D$2,$A$2:$A$10), $B$2:$B$10) [Formula]
Returns the value 59, refer Table 6. The formula does a case sensitive search for the lookup value (“jack” in cell D2) in column A, starting from the bottom of the range “A2:A10”, and returns the first corresponding value in column B, in the table array “A2:B10”. It effectively does a case sensitive Vlookup of: =VLOOKUP(D2, A2:B10,2, FALSE), but returns the LAST OCCURRENCE of the lookup value in cell D2.
FIND function Syntax: FIND(find_text, within_text, start_num). Find_text is the text you want to find. Within_text is the text containing the text you want to find. Start_num specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1. FIND is case sensitive.
Formula Part =FIND($D$2, $A$2:$A$10) evaluates to: {#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!, #VALUE!,#VALUE!, 1,#VALUE!}, because the 8th row (cell A9) in the array A2:A10 contains “jack”. It may be noted that each occurrence of the lookup value “jack” will return 1 in this array.
LOOKUP function Syntax: LOOKUP(lookup_value, lookup_vector, result_vector). A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range.
9.99999999999999E+307 is the Largest +ive number in Excel. If the Look_Up value is greater than the largest value in the Look_Up Array, it will return the last number in the array, even if the Look_Up Array is not sorted in ascending order, which is required for the LOOKUP formula. LOOKUP seemingly starts from the bottom of the Look-Up Array and searches vertically up, presuming the range is in ascending order and the last number being the largest in the Look-Up Array. Hence 9.99999999999999E+307 has been arbitrarily chosen as the Look-Up value, which you know will be greater than the value you are looking for. If the range has no numeric value, the LOOKUP formula will return the #N/A error.
The LOOKUP formula in this example looks for the last numerical value in the array {#VALUE!, #VALUE!,#VALUE!, #VALUE!,#VALUE!, #VALUE!,#VALUE!, 1,#VALUE!}, presuming the range is in ascending order and the last number being the largest (as explained above), and then returns the corresponding value from the result_vector “B2:B8”, which is 59. If this array has multiple occurrences of the number 1 (ie. if “jack” has multiple occurences in the range “A2:A10”), the LOOKUP formula will still look for the last numerical value in the array and return its corresponding value from “B2:B10”.
NOTE: If cell D2 (Lookup Value) is blank, the formula will return the value in the last cell of range “B2:B10”, which is 69. To avoid this, you can modify the formula to:
=IF($D$2=”” , “Not Found” , LOOKUP(9.99999999999999E+307, FIND($D$2,$A$2:$A$10), $B$2:$B$10)).
———————————————————————————————————————–
Case Sensitive Vlookup – Find the Largest or Smallest Corresponding Value
Example 7:
=MAX(IF(EXACT(A$2:A$10, $D$2), B$2:B$10)) [Formula]
Enter as an array formula (CTRL+SHIFT+ENTER). Returns the value 59, refer Table 7. The formula does a case sensitive search for the lookup value (“jack” in cell D2) in column A and returns the Largest corresponding value in column B, in the table array “A2:B10”. In case of multiple occurrences of “jack” in column A, this formula returns the Largest corresponding value from column B, irrespective of the position of occurrence of “jack”.
Example 8:
=MIN(IF(EXACT($A$2:$A$10, $D$2), $B$2:$B$10)) [Formula]
Enter as an array formula (CTRL+SHIFT+ENTER). Returns the value 32, refer Table 8. The formula does a case sensitive search for the lookup value (“jack” in cell D2) in column A and returns the Smallest corresponding value in column B, in the table array “A2:B10”. In case of multiple occurrences of “jack” in column A, this formula returns the Smallest corresponding value from column B, irrespective of the position of occurrence of “jack”.
Alternate formula to return the Smallest corresponding value:
=SMALL(IF(EXACT(A$2:A$10, $D$2), B$2:B$10), 1) [Formula]
Enter as an array formula (CTRL+SHIFT+ENTER). Returns the value 32.
In case corresponding values are not numericals, you will get a #NUM! error. You can modify the formula as below:
=IF(ISNUMBER(SMALL(IF(EXACT(A$2:A$10, $D$2), B$2:B$10), 1)),SMALL(IF(EXACT(A$2:A$10, $D$2), B$2:B$10), 1), “Not Numerical”) [Formula]
Enter as an array formula (CTRL+SHIFT+ENTER).
————————————————————————————————————————-
Case Sensitive Vlookup – Find the FIRST OCCURRENCE of a Value, using VLOOKUP & CODE functions
Example 9:
Consider a table array “A2:B10”, with names in column A and marks in column B. Lookup name is “jack” in cell D2, for which a case sensitive lookup is to be done.
Step 1: Create a helper column after column A, so that the helper column will become column B and the marks column will become column C. The lookup value cell will become E2 (from D2).
Step 2: Enter numeric codes (using excel CODE function) in column B, for each character in the name text mentioned in column A. Refer Table 9a.
Step 3: Enter numeric codes (using excel CODE function) in cell F2, for each character in the lookup name text mentioned in cell E2. Refer Table 9b.
Step 4: Enter the simple Vlookup formula in cell F3: =VLOOKUP(F2, B2:C10,2, FALSE). Refer Table 9c.