• 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 & look-up row with multiple condition

2

226Silver

New Member
Dear Excel Gurus - I am creating a simple address book on Excel with Name, Address, City, State and Zipcode.

However, I want to be able to look-up/search for the row number that contains part of the name (i.e. John of the "John Doe Inc." in the name column) AND lives in San Jose of CA (2-3 conditional criteria). In this look-up from my sample worksheet following - it will come back with Row "2".
Then if then search next just on the name only this time (1 conditional criteria) it will then give me Row "6" of "John Smith Car Wash" in Paoli of PA.

I am trying to write a macro to do that but could not get it works right.

Please advise .... Thanks.

Name Address City State Zipcode
John Doe Dinner 546 Main Street San Jose CA 96234
Marco Web Design Inc. 7 Shortfield Drive Neward DE 21657
IHG Spa Club 67 North River Road Columbus OH 63459
Pheonixville Dinner 33 High Street Pheonixville PA 19980
John Smith Car Wash 365 West Court Paoli PA 19567
Newton Web Design LTD 100 West Port Blvd Chesterfield MO 76544
 
Somendra & Deepak - Thanks. Your formulas work well and very flexible in the way they search on any part of the name or message. They did solve a good chunk of my problem. However - will they work with more than 2 conditions such as - in your sample spreadsheets that you have posted- I want to search for someone lives in "Napoli" (part of the Address tab) WITH "Medium" priority (part of Messages tab) ?. I tried 2 text words in your search field - it did not work.
 
Hello SM - I tried to "Upload a File" to show you what I tried to do - but then I got the error of:
The following error occurred
There was a problem uploading your file.

Address Worksheet1.xlsm
What can/should I do to get the file to you ?
 
Hi ,

Is your file size more than 1 MB ? If so , you cannot upload it to this forum ; instead you will have use any public file-sharing website such as RapidShare , DropBox ,... give others access to the file , and post the access link here , in this same thread.

If your file is not more than 1 MB in size , retry and you may be able to upload it.

Narayan
 
Thanks Narayan for the tip - As a new user, I am still learning.
Here is the file

https://www.dropbox.com/s/t2dje31e70xac8x/Address Worksheet1.xlsm

that I want to create Search macros (on Search tab) to search with more that 1 condition (i.e. Name and City) - Right now it can only search by Name. Also the "Next Search" is if it is NOT the right record - then it will go to the next record that match with that search condition.
It seems to be very fundamental - but I can not make it works.
 
@226Silver

See the attached file. Go through the various codes & named ranges used. I cannot comment on the speed of the process as the major job here is done by formulas and changing search values and some other features are done by macro. SO speed will depend upon your database size to a large extent.

Regards,
 

Attachments

Somendra; Very cool - It works well - I don't think the process speed would be a problem. The database size is not that large. Anyway - Thanks for the help.
 
@Somendra;

The file that you sent works beautiful but when I try to emulate into my spreadsheet with similar functions and macros (I copied yours and modified accordingly) it did not work for me at all. I am sure I did something wrong. Can you take a look to see what I have missed and did wrong ?
What I try to do is to get the "Search Next" button work to look for that row in the "Contacts" worksheet and display them. The search is based on 1-4 conditions (Name, City, State or/and Skills)

https://www.dropbox.com/s/6c84ptpa5wrcdyp/Test_226Silver.xlsm


Truly appreciated.
 
Hi 226Silver,

I did not found my code, in the file moreover where are the formulas?

Are you talking about formulas in L16:L19?

Regards,
 
Hello Somendra;
Thanks for a quick response. yes I built the formula into L16:L19 but keep getting error on Table1(Name1) block so I could not turn it on. Also your Next Search macro was emulated into the "Search Next" block so it does not do much. The idea is to find this person under these criteria and display their information on B Row.

Cheers,
 
Hello SM;
I have been out as well. I finally have a chance to work on it today to see if I can link the formula to the search table. Thanks for your help. Will let you know how it works out.
 
You must log in or register to reply here.
Back
Top