Table of Contents
A case sensitive VLOOKUP in Google Sheets refers to the usage of the VLOOKUP function in a spreadsheet while considering the sensitivity of uppercase and lowercase letters. This means that when searching for a value in a specific column, the function will only return a match if the case of the letters in the search criteria and the data in the column are exactly the same. To use a case sensitive VLOOKUP in Google Sheets, the lookup range and search criteria must be set to exact match mode by using the EXACT function. This can be helpful in situations where data may contain variations in capitalization and the accuracy of the search results is critical.
Use a Case Sensitive VLOOKUP in Google Sheets
You can use the following basic formula to perform a case sensitive VLOOKUP in Google Sheets:
=INDEX(B2:B10,MATCH(TRUE,EXACT(G2,A2:A10),0))
This particular formula finds the exact value in cell G2 in the range A2:A10 and returns the corresponding value in the range B2:B10.
The following example shows how to use this formula in practice.
Example: Case Sensitive VLOOKUP in Google Sheets
Suppose we have the following dataset that shows the number of sales made by various salesmen at a company:
Now suppose we attempt to use the following VLOOKUP formula to look up “Andy” and return his number of sales:
=VLOOKUP(D2,A2:B10,2)
This formula incorrectly returns the number of sales for ANDY instead of Andy:
Instead, we need to use the following formula that can perform a case-sensitive VLOOKUP:
=INDEX(B2:B10,MATCH(TRUE,EXACT(G2,A2:A10),0))
This formula correctly returns the number of sales for Andy, which turns out to be 29:
The formula correctly returns the number of sales for Andy instead of ANDY.
Additional Resources
Cite this article
stats writer (2024). How can I use a case sensitive VLOOKUP in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-a-case-sensitive-vlookup-in-google-sheets/
stats writer. "How can I use a case sensitive VLOOKUP in Google Sheets?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-a-case-sensitive-vlookup-in-google-sheets/.
stats writer. "How can I use a case sensitive VLOOKUP in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-a-case-sensitive-vlookup-in-google-sheets/.
stats writer (2024) 'How can I use a case sensitive VLOOKUP in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-a-case-sensitive-vlookup-in-google-sheets/.
[1] stats writer, "How can I use a case sensitive VLOOKUP in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use a case sensitive VLOOKUP in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.