Is Countif the correct function to match a substring in a range to a selected value

Hi,

I have a working formula

Please Login or Register  to view this content.

Now the formula works when a cell in column OC in worksheet OfferDetails (which is what the named range Offerinoc refers to) has just one value - example - 685-cf-18A .
So if rngOC = 685-cf-18A , the formula works.
If it has say, three , 685-cf-18A ,685-cf-24b, 685-cf-11c then it doesn't work.

I need to be able to match one of these three to rngOC.

Please Login or Register  to view this content.

by replacing Offerinoc=rngOC with (IF(COUNTIF(Offerinoc, "*"&rngOC&"*")>0,rngOC)=rngOC

Also tried

Please Login or Register  to view this content.

but the cell remains blank.

I dont know if i am using the wrong function -countif ?

What could be the error. Would very much be grateful for help !

thanks.
anandi

The first two formula boxes are empty.
Post a sample file.

---

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

Your IF is returning FALSE (=0) and becomming the smallest one

SMALL(IF(Offerinoc=rngOC ,......,"")

What blocked error? Are you trying to post a link? That won't be allowed for you.

Follow the instructions in post #2.

I wasnt trying to post a link but the excel sheet. But even if i try to add code i get this message : Access Denied - Sucuri Website Firewall

That is a wellknow problem, which accurs once in a while.

I had the same problem a while ago.

To my opinion there is no other option, then to wait the forum solved the problem for you.

To avoid firewall, anywhere with "larger" or "smaller", try " > " (with space before and after)

Please Login or Register  to view this content.

If you are unable to attach the code to your post, then attach a workbook instead with the code in it

You can't as a new member post a link. You need to follow the instructions at the top of the page. Try again, please.

AliGW

OP said

I wasnt trying to post a link but the excel sheet. But even if i try to add code i get this message : Access Denied - Sucuri Website Firewall

I had this problem a while ago and had to wait the "moderators" solved that problem.

No It does not help!. Try to read instruction in upper yellow banner of the page to know how to attach a WS

Hello Anandi,

Try this way:

Replace Offerinoc=rngOC in IF formula to;

ISNUMBER(SEARCH(rngOC,Offerinoc))

Are you able to attach a .txt file containing the code? It is not usual for an attachment to trigger the Sucuri firewall - maybe it’s to do with the name of the file?

Can you see the attached text file?
I have copied my original post in there.

Yes, thanks. This will help.

I still don't understand why you cannot attach the Excel workbook, though - what is the name of the file? Can you change it to simply 'test' and try attaching again?

If you remove IFERROR from your formulae, what error message do you get? Is it #N/A!, #VALUE! or something else?

Anandis,

Did you try as I suggested in my previous post?

Hi,

So the first modified option , without IFERROR, returns the first value in the named range . The second option returns #NUM!

Thanks.

OK. Have you tried attaching the workbook again as I suggested above? We really need to see it. If you can't attach it here, then you could now (with 11 posts) post a link to a file-sharing site.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

Sorry I missed your message. Yes I have tried ISNUMBER(SEARCH) but it doesnt work as OfferinOC is a named range for an entire column of data. Earlier, the column would have only 1 value but now it has multiple values.
Thanks.

Originally Posted by anandis

...OfferinOC is a named range for an entire column of data...

Your INDEX function referring to a table named OfferDetails & the named range OfferinOC refers to entire column?. Replace your named range to actual column in the table. ie;

ISNUMBER(SEARCH(rngOC,OfferDetails[Column Name]))

How about in D14 then copy down and accross:

Please Login or Register  to view this content.

In B14, copy down & across (Array Formula)

=IFERROR(INDEX(OfferDetails[#Data],SMALL(IF(ISNUMBER(SEARCH(rngOC,OfferDetails[OC '#])),ROW(OfferDetails[#Data])-ROW(OfferDetails[#Headers])), ROW(1:1)), MATCH(B$13, OfferDetails[#Headers], 0)),"")

See atahment where I apply the formula.

Originally Posted by anandis

...why search worked and countif failed too?

COUNTIF(RANGE,criteria) produces a single result, not a range of 1/0