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","")))
``` Apr 10 in Others 137 views

## 1 answer to this question.

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 • 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() • Formula used in cell A11

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

With One Single Array Formula: • Formula used in cell A11

```=MAP(B11:B15,LAMBDA(m,LET(x,TEXTSPLIT(m," "),
FILTER(x,ISNUMBER(XMATCH(x,Search_Word))))))```
• 38,010 points

## How to build a custom column formula in Power query that will retrieve Image and load it to Excel to display that image from the specified column

Make sure ignore privacy is selected in ...READ MORE

## 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

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

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

## 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

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

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

## Convert Rows to Columns with values in Excel using custom format

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

## IF - ELSE IF - ELSE Structure in Excel

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