Partial Text Search in Range - Index/Match

Rhonda Mitchell

I have searched for solutions and getting weary.

Challenge find PO# (a 6 digit alpha-numeric) in a string of text (GL_Remark on another sheet to index the actual value cleared general ledger. I had this formula working in Excel:

=INDEX(General Ledger_AmtPaid,MATCH("*"&[@[PO/INV]]&"*",GL_Remark,0))

In SmartSheet

Only finds exact match, ie. KAM307 when nothing else in cell:

=INDEX({General Ledger_AmtPaid}, MATCH([PO/INV]2, {General Ledger_GL Remark 4}, 0))

=INDEX(COLLECT({General Ledger_AmtPaid}, {General Ledger_GL Remark}, [PO/INV]1), 1)

Unparsable:

=INDEX({General Ledger_AmtPaid}, match("*"&[PO/INV]1&"*",FIND({General Ledger_GL Remark})

Invalid Column Value:

=INDEX(COLLECT({General Ledger_AmtPaid}, {General Ledger_GL Remark}, [PO/INV]1), 0)

=INDEX({General Ledger_AmtPaid}, COLLECT({General Ledger_GL Remark}, {General Ledger_GL Remark}, [PO/INV]1))

Invalid Data Type:

=if(FIND([PO/INV]1, {General Ledger_GL Remark},1),"Cleared","Unpaid")

All fields are text/number and did try other types.

I also get invalid data type for a simple Find:

=FIND("KAM", {General Ledger_KAM Range 1})

Any help is appreciated!

 

Paul Newcome

You'll need to find a way to build in a FIND function. I know it is possible, but it has been a while since I have needed it. If I remember correctly I also ended up incorporation a JOIN(COLLECT( set as well. I will get back to you as soon as I work out the details.

Rhonda Mitchell

Is there a formula to TRIM (since TRIM is not available) those not meeting the formula when using MID?

Was successful using LEFT, RIGHT but want to eliminate those not matching criteria.

Paul Newcome

Are there any consistencies in where within the text string you will find the PO#?

Eric M Oliveira

Hello,

 

We'd be happy to assist. You'll find that most formula syntax's when coming from Excel to Smartsheet are almost exactly the same, they often only differ in how the column references are structured. 

 

In theory, based on the information provided the formulas should work as desired. If you're currently experiencing an error with the formulas provided it may be that the cross-sheet references are not including the desired ranges accurately. You may want to confirm these sections of the formulas are including the columns need to be referenced by the formulas. 

 

These Help Center article outline how to utilize the INDEX, MATCH, and FIND syntaxes.

 

INDEX:https://help.smartsheet.com/function/index

MATCH: https://help.smartsheet.com/function/match

FIND: https://help.smartsheet.com/function/find

 

If I've misunderstood your desired goal it may be best to provide further details on the exact goal you're looking to achieve with these formulas. Please also provide screenshots of the source sheet and recipient sheet outlining the columns needed to be included in the formula and overall sheet layout. 

 

Have a wonderful day,

Eric  -  Smartsheet Technical Support

Paul Newcome

FIND functions do not work within an INDEX/MATCH formula.

 

Something along the lines of

 

=JOIN(COLLECT({Range to display data from}, {PO# Range}, FIND([PO#]@row, @cell) > 0))

 

This will join all of the cells from the first range where the PO# is found in the second range regardless of where in the text string it is within the cell.

 

If there are too many cells being pulled because the PO# is listed multiple times, you can build additional ranges and criteria into the COLLECT function to narrow it down.

Danielle Arteaga

Hey @Paul Newcome - this answer was a HUGE help to me on a different issue. Works like a charm. The ONLY thing is ... my results are bunched together in my cell without any separation.

For example, I use this formula to find all of the accounts assigned to a specific seller. The formula returns something like this:

ABC CompanyXYZ CompanyLMN Company

Is there any way to insert some kind of delimiter between the results in the join? I can't figure out the syntax for this. Any insight would be very appreciated!

EDITED TO ADD: Nevermind. ;-) I got it.

=JOIN(COLLECT({Territory User IDs Accounts}, {Territory User IDs Group}, FIND([User Name]@row, @cell) > 0), ",")

OF COURSE the comma went at the end ... where I didn't try to put it until AFTER I typed this note. :-)

Andrée Starå

Hi @Danielle Arteaga

A tip.

You can also use the CHAR function, and CHAR(10) for a new line.

https://help.smartsheet.com/function/char

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!