Help Me Repair a Formula

[Mayanwolfe]

Hi all!

I found a lovely little formula to help me search a list for a keyword or number and then return multiple matching results to populate a dropdown box. It worked brilliantly, but, unfortunately, in all my machinations I ended up breaking it. Now, I don't know what goes in the space that has been replaced by a #REF! value. Has anyone worked with this before?

The user enters a search term or number in cell I15. The search list is in Column W.

In Column S I have:

=SUMPRODUCT(--(ISNUMBER(SEARCH($I$15,$W$2:$W$1050))))

and reading off that in Column T I have:

=IF(ROWS($R$15:R15)>$Q$15,"",INDEX($W$2:$W$1998, SMALL(IF(ISNUMBER(SEARCH($I$15,$W$2:$W$1050)), MATCH(ROW($W$2:$W$1050), ROW($W$2:$W$1050))), ROW(#REF!))))

I want to figure out what should replace that #REF at the end of the second code string. Any help would be appreciated, thanks in advance!

 

[Andrew Poulsom]

Try A1.

 

[Mayanwolfe]

I gave that a try, but the only entry it returns is the value from the search list that it is parallel to, which is not remotely a match.

 

[AvcGJS]

can you not get an older copy of the spreadsheet recovered by the I.T folks at your office?

 

[Mayanwolfe]

Never mind, I completely forgot that it was an array formula! I entered A1 into the space, hit Control+Enter, then dragged it down, and voila! All fixed. Thank you so much for your help, Mr. Poulsom!