• 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.

Search & lookup function in Data Validation

rrocker1405

Member
Hi,

Am trying to use both search and lookup function in data validation but not able to use it. Any suggestions to use other combinations which can help me search a text and lookup for possible options to list values?

=IFERROR(LOOKUP(2;1/SEARCH(C3;a1:a20);b1:b20);"")

Thanks in advance.

Kind regards,
A!
 

r1c1

Administrator
Staff member
Hi A!, I assume you are using this formula inside custom data validation as a rule. If so, data validation function must return TRUE / FALSE only.

Change it to NOT(ISERROR(LOOKUP(2;1/SEARCH(C3;a1:a20);b1:b20))) and try.
 

rrocker1405

Member
Thank you for the quick response, am trying to list the possible options using data validation if text matches in a list of column. Having true/false will not work.

I can use combo box to do this but i have to place as many as 100 of them. so was thinking if there is a easier way to do it using data validation.

Kind regards,
A!
 

r1c1

Administrator
Staff member
If you are trying to have auto complete / partial match based on user input, use Active X combo box. If I misunderstood your question, please upload a sample file and explain what you are trying to do so I (or someone else) can guide you.
 
Top