• 543K Members
• 3,654 Online
• 647K Conversations
SOLVED

Highlighted
New Contributor

6 Replies
Highlighted

# Re: How to : create a formula that look for a certain keyword, match from a list and provide output

Can you do the same thing as this video but instead of looking for an exact match it search if A1 (in return reference sheet) contains text from the range of customer ID that he selected (in customers sheet)

https://youtu.be/TKcdtSmZvyo
Highlighted

# Re: How to : create a formula that look for a certain keyword, match from a list and provide output

@moloco , do you mean extract code from the name like "E011223_Centrum_DSR_Om8" (DSR in this case) and based on it return Deliverable Name?

Highlighted

Yes
Highlighted
Solution

# Re: How to : create a formula that look for a certain keyword, match from a list and provide output

@moloco , that could be like

```=IFERROR(INDEX(\$G\$3:\$G\$9,
MATCH(MID(A3,
AGGREGATE(14,6,1/(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)="_")*ROW(INDIRECT("1:"&LEN(A3))),2)+1,
AGGREGATE(14,6,1/(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)="_")*ROW(INDIRECT("1:"&LEN(A3))),1) -
AGGREGATE(14,6,1/(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)="_")*ROW(INDIRECT("1:"&LEN(A3))),2)-1),
\$F\$3:\$F\$9,0)),"no such")```

AGGREGATE finds last and previous to it position of "_" between which we extract the code

Highlighted

Gracias !
Highlighted

# Re: How to : create a formula that look for a certain keyword, match from a list and provide output

@moloco , you are welcome

Related Conversations
SharePoint Lists - Expanded View Roadmap ID: 57302
Kotus-Tech in SharePoint on
9 Replies
Multi-value fields display no separator
Florian Hein in SharePoint on
1 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies