VLOOKUP help that is case sensitive, exact, and allows text and numbers

Hello,

I am needing help creating a formula that will search if column A of sheet 1 values exist in sheet 2's entire column A range. If it exists, output a "Yes" in corresponding sheet 1 column B next to the searched value. Otherwise output a "No". I have searched for days trying to find the perfect formula for this. Seemed simple at first however, the difficult part is the formula must look for values that are case sensitive, exact, and allows for a mix of numbers and text in the search.

Example:

Sheet 1
Column A
Test1
test1
Te1st
te1st

Sheet 2
Column A
Test1abc
ABCTest1
Te1st
t1est

The desired result would output "Yes" for Te1st. "No" for the others.

This will give a TRUE/FALSE result instead of Yes/No

=ISNUMBER(LOOKUP(2,1/EXACT(A2,Sheet2!$A$2:$A$5)))

If you must have yes/no, just add an if

=IF(ISNUMBER(LOOKUP(2,1/EXACT(A2,Sheet2!$A$2:$A$5))),"Yes","No")

Hello Jonmo1,

Thanks for the quick response. The formula you provided almost got me there. Seems that it is not case sensitive when testing.

Try something like...

B2, Sheet1...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,FIND(A2,Sheet2!$A$2:$A$6))),"Yes","No")

Curious, what does the 9.9999999 number do in your suggestion?

Originally Posted by dafacta

Curious, what does the 9.9999999 number do in your suggestion?

LOOKUP(9.99999999999999E+307,Reference)

turns up the last numeric value from Reference if any. It's of significance that the look up value cannot occur in references of interest to us. This 9.99999999999999E+307 is the largest allowed positive number in Excel and for that reason is a good candidate.

See:

http://www.mrexcel.com/forum/excel-q...9999999-a.html
http://www.mrexcel.com/forum/excel-q...-returned.html (post #7)

Thanks for everyone's help. Decided to manipulate the search range data to simplify things a bit for the search. Suggestions above worked perfectly.