Help with fuzzy match formula

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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…