Find specific term in a text string and return that term in the previous column

0 votes

The initial return is functioning for some reason that I cannot fathom, but when I pull down, the other results are returned as #VALUE!.

=IF(FIND("Drummondville",B3)>0,"Drummondville",
IF(FIND("Saint-Germain-de-grantham",B3)>0,"Saint-Germain-de-grantham",
IF(FIND("Saint-cyrille-de-wendover",B3)>0,"Saint-cyrille-de-wendover","")))

Excel snap

Apr 10, 2023 in Others by narikkadan
• 63,420 points
277 views

1 answer to this question.

0 votes

Try: You can either hard code the search words or create a reference range and name it as Search_Word and use with in a LOOKUP() Function

enter image description here


• Formula used in cell A2 --> Search Words hard-coded.

=LOOKUP(9^9,
SEARCH({"Drummondville","Saint-cyrille-de-wendover","Saint-Germain-de-grantham"},B2),
{"Drummondville","Saint-cyrille-de-wendover","Saint-Germain-de-grantham"})

• Formula used in cell A11 --> When Search Key Words use from a define range

=LOOKUP(9^9,SEARCH(Search_Word,B11),Search_Word)

With MS365 you could try using FILTER() XMATCH() & TEXTSPLIT()

enter image description here


• Formula used in cell A11

=LET(x,TEXTSPLIT(B11," "),
FILTER(x,ISNUMBER(XMATCH(x,Search_Word))))

With One Single Array Formula:

enter image description here


• Formula used in cell A11

=MAP(B11:B15,LAMBDA(m,LET(x,TEXTSPLIT(m," "),
FILTER(x,ISNUMBER(XMATCH(x,Search_Word))))))
answered Apr 10, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,420 points
955 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,420 points
1,299 views
0 votes
1 answer

How to find the last row in a column using openpyxl normal workbook?

ws.max_row will give you the number of rows ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,420 points
4,879 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,237 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,424 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
698 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,331 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,127 views
0 votes
1 answer

Loop through each cell in a given range and change the value depending on value in a column in the same row

Use match() and if() without code at ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,010 points
489 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP