Help with fuzzy match formula

L_123

I have no idea how the program thinks that the number 2 even approximately resembles the word "Sparks Corp". I want to use fuzzy math to find the closest vendor name to the one that is typed in, and was hoping to use the approximate match, but am having a hard time understanding what the program believes is approximate. I think a "Find" function might work for this as well, but am hitting a wall on implementation.

 

 

Edit: I realize I mixed up the text 0=exact match 1=approximate. Same issue still applies though

Shaine Greenwood

Hi Luke,

The optional search_type parameters (1, 0, -1) in MATCH require that you have your range sorted, and may not work as well when you have a mixture of different data types. More info on MATCH is available in the help center: https://help.smartsheet.com/function/match

Note that if you sort and save, the sort becomes permanent. 

L_123

I'd rather not have to sort each time, and don't like the idea of it failing if the sheet doesn't get sorted. There will be thousands of rows when complete, and new rows added all of the time.

The other path I was thinking of was using lower() and find() to parse the text, but the issue becomes how do I return the location.

Shaine Greenwood

FIND will return the starting position of a character in a cell, not the row index. (I don't think that's what you're looking for.)

One idea is to create a new text/number column with a =LOWER(<cell>) function in each cell that references each adjacent cell in your first column, then perform MATCH(LOWER(<cell>), range, search_type)

I'm not sure if there's another way to do what you're wanting other than that.