Make cells in Excell case sensitive

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
 
J

Jacob Skaria

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
 
G

Gary''s Student

Use the
=EXACT()
worksheet function to compare items in a case-sensitive fashion.
 
L

Luke M

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".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top