Comparing case sensitive values from 2 different columns

Hi,

I have two columns I want to compare and find out if the values in the first column match the values in the second column. It has to be case sensitive. I have tried using MATCH and VLOOKUP but these are not case sensitive. I have tried using EXACT but it does not work for what I am trying to achieve. Its a one to many relationship, with the 2nd column having the one, and the 1st column having the many. Column 1 has the value a,A,A,B, b,B ,a. Column 2 has A and B. I want to know in Column 1 when it has a matching exact value in column 2. The EXACT formula I am using is {=EXACT(A1,B$1:B$2)}. It does not seem to like the B$1 to B$2 part. Hopefully the attached image explains it better. Version = 2013

Excel.PNG

Thanks

Jason

I don't understand the logic, when there are blanks in Column2.

Originally Posted by kersplash

I don't understand the logic, when there are blanks in Column2.

Column 2 is purely the values I am checking for. These are A and B. So I would like to know if the Values in Column 1 match any of the values in column 2. The return True or False

Hi,

This should do what you want:

Excel 2016 (Windows) 64 bit

	A	B	C
1	A	A	TRUE
2	a	B	FALSE
3	A		TRUE
4	B		TRUE
5	A		TRUE
6	B		TRUE
7	A		TRUE

Sheet: Sheet23

Excel 2016 (Windows) 64 bit

	C
1	=ISNUMBER(LOOKUP(2,1/FIND(A1,B$1:B$2)))

Sheet: Sheet23

Formula copied down.

OK.

First row....A=A=TRUE...got it
Second row....a≠B=FALSE...got it
Third row....A≠(blank)=TRUE...I don't got it.

OK, I understand now, ignore my attempt.

Originally Posted by jtakw

Hi,

This should do what you want:

Excel 2016 (Windows) 64 bit

	A	B	C
1	A	A	TRUE
2	a	B	FALSE
3	A		TRUE
4	B		TRUE
5	A		TRUE
6	B		TRUE
7	A		TRUE

Sheet: Sheet23

Excel 2016 (Windows) 64 bit

	C
1	=ISNUMBER(LOOKUP(2,1/FIND(A1,B$1:B$2)))

Sheet: Sheet23

Formula copied down.

That is great and it work thanks. Just out of curiosity what does the / between the 1 and the FIND do?

Maybe,

=EXACT(A1,$B$1)+EXACT(A1,$B$2)

Or,

=SUMPRODUCT(0+EXACT(A1,B$1:B$2))

Regards
Bosco
Bosco

Originally Posted by JasonBennett

That is great and it work thanks. Just out of curiosity what does the / between the 1 and the FIND do?

Actually, in this particular situation, we don't "need" the 1/ before FIND since we're dealing with Single characters in Both Columns.
However, in cases where the FIND may result in a number Greater than 1, 1/ (one divide) the result of the FIND ensures that the number Stays at 1 or below, and the LOOKUP is looking for 2, which it won't find, and thus returns the last match if there is one. The ISNUMBER just turns the result into TRUE/FALSE as you requested.

If you found my posts helpful, add rep would be appreciated, click the "Star" at the lower left corner of the posts you found helpful, thank you.