VBA Fuzzy Lookup (Find) - Case Sensitive Issue

Hi,

I have a function that performs a "fuzzy" lookup/find against a set of data to find matching values.

For example, for each cell in Field 1, the value in that cell is looked up against the entire range of data in Field 2,
and where there is a "Fuzzy" Find/match, that value is populated on the same row in the column named "FUZZY FIND".
I have a separate function that calculates a similarity score in column D.

To illustrate this: Cell A2 = the number 123, well, 123 also exists in Field2 (B5), so on Cell C2 (FUZZY FIND column), the value 123 appears which is a 100% match.

FIELD1	FIELD2	FUZZY FIND	SIMILARITY SCORE
123	this is pretty awesome	123	100%
7899	UPPERCASE	789	75%
hello	hell	hell	80%
this is awesome	123	this is pretty awesome	68%
ALEX	789	ALEX	100%
uppercase	karl		0%
alex	ALEX		0%

The problem I am running into however is that my FuzzyFind function is case sensitive, whereas I would like it to ignore the case -- or perhaps consider all of the data to be upper or lower case, if that's the easier solution.

For example, in cell A6 I have the name "ALEX" appear, which also appears in Field 2 exactly as "ALEX", so in cell C6, the corresponding Fuzzy Find = "ALEX", which is rated as a 100% match.

However, in cell A8, the name "alex" appears as lower case, of which there is no lowercase variant in Column 2, so in cell C8, my Fuzzy Find value is simply blank, with a 0% match rate.
Rather, I would like "alex" and "ALEX" to both be treated equally, and correspond to a 100% match, so my "FUZZY FIND" value for "alex" should appear as "ALEX" in cell C8, instead of the blank currently there.

I've played around with the code in the function used to generate the values in Col C, but can't figure out a way to get it to either ignore case or normalize the case (all UPPER or lower) against both columns being evaluated.

I've attached a sample file with the same table as above (on Sheet2) that I hope someone can take a look at, and lend a helping hand.

Oh, and here's my code for the FuzzyFind Function that's used to generate the values in Col C:

Please Login or Register  to view this content.

Thanks in advance!

maybe just put some Lcase around your variables

Please Login or Register  to view this content.

Cross-posted without a link @ https://www.mrexcel.com/forum/excel-...ive-issue.html