Right. This is because when written this way, the formula is perceived as `=SEARCH(B1;A1)`

or for your data `=SEARCH("Hero";"Evil Character")`

. Try setting `A1`

to **Hero** and this formula will work.

If you enter the same formula as an array formula (with **Ctrl+Shift+Enter** instead of the usual **Enter**), you will get an array of three #VALUE! errors. This is because you will actually get three formulas like `=SEARCH(B1;A1)`

, `=SEARCH(B2;A2)`

and `=SEARCH(B3;A3)`

, and there are no values to search in cells `A2:A3`

.

But if you enter `{=SEARCH(B1:B3,$A$1)}`

, you will get an array like

```
#VALUE!
1
#VALUE!
```

Should not be doing that. Just take my word for it - more than two IF() in one formula is overkill. This formula is very difficult to read, understand and, if necessary, correct. Just go back to **the formula with AGGREGATE()**