Make cells in Excell case sensitive

  • Thread starter Thread starter Mliamos
  • Start date Start date
M

Mliamos

I was wondering if there is a way to make the cells in Excell case sensitve,
so when your using look up functions it only returns the cell with the
correct upper and lowercase letters
 
Try the below test scenario using INDEX() and MATCH()

ColA ColB ColC ColD
One 1 ONE =formula
one 2
ONE 3

Formula in D1
Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=INDEX(B1:B10,MATCH(TRUE,EXACT(C1,A1:A10),0))


If this post helps click Yes
 
Use the
=EXACT()
worksheet function to compare items in a case-sensitive fashion.
 
EXACT and FIND are two functions that are case-sensitive. You can usually
combine these with other functions to create case-sensitive lookups.

Example:
........A..........B
1..Name.....Value
2..John.........5
3..john.........6

=INDEX(B:B,SUMPRODUCT(ROW(A1:A10),--EXACT("john",A1:A10)))

This formula would return a value of 6, by finding exact match to "john" in
column A, taking the row number, and the row number in the INDEX function.
Note that this assumes that there is only one instance of "john".
 
Back
Top