Use of formula =IF(ISNUMBER(SEARCH...

[Szyfrow]

Hi, can anybody clarify what's wrong with this formula:
=IF(ISNUMBER(SEARCH(A1;D:D));E1;"no hit")

The idea is: test if the value of cell A1 is present in column D.
If so, fill the cell with the content of cell E1
Otherwise with "no hit".

The formula produces 'No hit', so I guess the IF test results in FALSE.

Thanks for your help!

[63falcondude]

Try something like this:

=IF(COUNTIF(D:D,"*"&A1&"*"),E1,"no hit")

Edit: Switch the commas with semi-colons if that's what your regional settings require.

[romperstomper]

That formula returns an array of results, not a single result. Try:

=IF(COUNTIF(D:D;"*"&A1&"*")>0;E1;"no hit")

[WideBoyDixon]

Hi there,

SEARCH() is for finding a substring of text in a cell; you need to use MATCH() I think:

=IF(ISNUMBER(MATCH(A1;D:D;0));E1;"no hit")

NOTE: This only works if you're matching the whole value. If it might be a partial match, use the formula above.

WBD

[Szyfrow]

Hi, Thanks for your suggestions. Momentarily I'm away from my computer. Will get back to you soon.

[romperstomper]

FWIW for a partial match with MATCH, use:

MATCH("*"&A1&"*";D:D;0)

[Szyfrow]

The formula =IF(COUNTIF(D:D,"*"&A1&"*"),E1,"no hit") does the job. Also thanks for the suggestions made by the other repliers.

However, on second thought I realize that I need to take the solution one step further – maybe even an approach different from '=IF(ISNUMBER(SEARCH...' will be is necessary.

What the formula needs to do is fill not only cell E1 but all cells of column E:E. Any suggestions?

[romperstomper]

Should they all be based on A1 as a criterion, or should they adjust for the same row in column E?

If they should all use A1 then:

=IF(COUNTIF(D:D,"*"&$A$1&"*"),E1,"no hit")

and copy down. If they should adjust then use the original version and copy that down.

[63falcondude]

Glad we could help.

If I am understanding you correctly, simply drag the formula down.

If I am not, consider uploading a small representative sample workbook along with the desired results (which you can enter manually) based on that sample.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[Szyfrow]

See attached sample file. The yellow values in the consecutive cells in column B:B (yellow) are an exact copy of those in column B:B (orange), which is of course not the intended result.

[Damz77]

If you want referencing then simply use this formula and drag down
=IFERROR(VLOOKUP(A1,D:E,2,0),"No hit")

[WideBoyDixon]

Then I think you can try:

Please Login or Register  to view this content.

In cell B1

WBD

[romperstomper]

I'm guessing you actually want a VLOOKUP:

=IFERROR(VLOOKUP(A1,D:E,2,FALSE),"no hit")

[Szyfrow]

Here is my feedback on the suggested solutions.

The following formulas perform the task I posted orginally. However, I can't find a way to exoend them in order to go 'one step further', as I expressed it.
=IF(COUNTIF(D:D;"*"&A1&"*");E1;"no hit")
=IF(ISNUMBER(MATCH(A1;D:D;0));E1;"no hit")
=IF(ISNUMBER(MATCH("*"&A1&"*";D:D;0));E1;"no hit")

The ones below did not.
=IF(COUNTIF(D:D;"*"&A1&"*")>0;E1;"no hit") - result: 'no hit' while actualy there was a hit
=IF(ISNUMBER(MATCH(A1;D:D;0));E1;"no hit") - result '0'

Thank all of you for thinking along.

[Szyfrow]

Feedback on suggested 'one step further' solutions.

The formulas below do exactly what I need. They also expand correctly when I use the fill handle.
=IFERROR(VLOOKUP(A1;D:E;2;0);"No hit")
=IFERROR(INDEX($E:$E;MATCH($A1;$D:$D;0));"no hit")
=IFERROR(VLOOKUP(A1;D:E;2;FALSE);"no hit")

Thanks to all for your help.