How to Use a Case Sensitive VLOOKUP in Google Sheets

To use a case-sensitive VLOOKUP in Google Sheets, you need to use a combination of the VLOOKUP function, QUERY function, and UPPER/LOWER functions. Unfortunately, VLOOKUP itself is not case-sensitive, but you can work around it by using these functions together. Here's how:

1. Organize your data in two columns or more (e.g., Column A contains the data you want to match, and Column B contains the data you want to return).
2. Create an additional column to convert the case of the data you want to match (e.g., Column C).
3. Use the UPPER or LOWER function to convert the text in Column A to uppercase or lowercase. For example, in cell C1, type =UPPER(A1) to convert the text in cell A1 to uppercase or =LOWER(A1) to convert it to lowercase.
4. Copy this function down the entire Column C to convert all the text in Column A.
5. Use the QUERY function to create a temporary table with the converted data. For example, in cell E1, type =QUERY(A:C, "select A, B where C is not null", 0).
6. Now, you can use the VLOOKUP function with the converted data. In a new cell, type =VLOOKUP(UPPER(search_key), E:F, 2, FALSE) or =VLOOKUP(LOWER(search_key), E:F, 2, FALSE), replacing "search_key" with the text you want to match.

Make sure to change the search_key to uppercase or lowercase using the UPPER or LOWER function, depending on the case conversion you used in step 3.

Here is an example:

Example

Let's say you have the following data in columns A and B:

A          B
Apple      5
banana     6
ORANGE     7

1. Use the =UPPER(A1) function in cell C1 and copy it down to the entire Column C. Your sheet should look like this:

A          B    C
Apple      5    APPLE
banana     6    BANANA
ORANGE     7    ORANGE

2. In cell E1, use the QUERY function: =QUERY(A:C, "select A, B where C is not null", 0). Your sheet should now look like this:

A          B    C       E          F
Apple      5    APPLE   Apple      5
banana     6    BANANA  banana     6
ORANGE     7    ORANGE  ORANGE     7

3. In a new cell, use the VLOOKUP function with the UPPER function: =VLOOKUP(UPPER("Apple"), E:F, 2, FALSE). This will return the value 5, as it matches the exact case of "APPLE".

Remember that this method is not perfect, and it may not work in all cases. However, it can be a helpful workaround for using a case-sensitive VLOOKUP in Google Sheets.