• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

iNDEX

S

shibulal

Member
=IF(COUNTIF($A$4:$A$15,"*"&$D$7&"*")<COLUMNS($E7:E7),"",INDEX($B$4:$B$15,SMALL(IF($A$4:$A$15="*"&$D$7&"*",ROW($A$4:$A$15)-MIN(ROW($A$4:$A$15))+1),COLUMN(A1))))

Why do this formula giving #NUM!
 
shibulal said:
=IF(COUNTIF($A$4:$A$15,"*"&$D$7&"*")<COLUMNS($E7:E7),"",INDEX($B$4:$B$15,SMALL(IF($A$4:$A$15="*"&$D$7&"*",ROW($A$4:$A$15)-MIN(ROW($A$4:$A$15))+1),COLUMN(A1))))

Why do this formula giving #NUM!
Click to expand...
Uploading sample file is better to find error
 
As Syedali said, a file would be helpful. There's nothing wrong with the formula structurally. However, I can guess what's wrong. While the COUNTIF function supports wildcards, the "=" in your IF function does not
...IF($A$4:$A$15="*"&$D$7&"*"...

This will look for a literal string containing asterisks. You would need to use the SEARCH/FIND to check. Perhaps like this?
=IF(COUNTIF($A$4:$A$15,"*"&$D$7&"*")<COLUMNS($E7:E7),"",INDEX($B$4:$B$15,SMALL(IF(ISNUMBER(SEARCH($D$7,$A$4:$A$15)),ROW($A$4:$A$15)-MIN(ROW($A$4:$A$15))+1),COLUMN(A1))))
 
You must log in or register to reply here.
Back
Top