Filtered Lookup List

[pbutcheck]

Now that people have learned I know Excel just SLIGHTLY better than the average joe, I have suddenly found myself the recipient of every Excel project in the business. So here's the latest:

Marketing/Mailing campaign involving 10s of thousands of people. Our team wants to keep track of everyone who responds to this one-time project (the project will span several days, but it isn't an on-going project).

Every time someone responds to our campaign (in writing), we need to call these individuals back. Therefore, we'll need to track 1) who was contacted, 2) who responded, 3) who called back the individuals and what their notes read.

I found a Contact Management template for this project and implemented it. It is nearly perfect. It can list all of the contactees, their address, etc. When they respond, we can fill out YES/NO fields and document their phone #s and Email address, etc.

It even has a Call Log sheet that will lookup the name and retrieve the phone/email information - and store a log of who called, when, and what their notes are. I'm 99% there.

My questions are regarding the fine-tuning you experts may be able to provide.

1) when looking up the source information, it will give a list of all 10k+ people. But we really only need to see the people who've responded to YES on the PURCHASED column. Is there a way to filter the lookup to only pull people IF column I=YES?

If that is not possible, do you have another idea/solution that would help?

My areas of concerns are:

1. several people will be working on this; each with very low levels of excel experience. Trying to keep it user-friendly
2. This is a rolling project for several weeks/months. There won't be a 'final' date in which all the data is received and THEN we can do something to move forward. All data will be worked on and managed during the entire project; a rolling collection and marketing campaign.

Master List worksheet:

Name <tbody> </tbody>

Address

City

State

Zipcode

County

Campaign Date <tbody> </tbody>

Purchased

If yes, When? <tbody> </tbody>

Option 1

Option 2

Phone 1

Phone 2 <tbody> </tbody>

Email

Date Rcvd

No Contact

__TEST DATA 1

1234 Somewhere Street

Glenolden

PA

19036

Delaware

1/1/2015

Yes

Yes

Yes

215.555.1212

610.555.1212

test@email.com

8/25/2015

__TEST DATA 3

1236 Somewhere Street

Perkasie

PA

18944

Bucks

8/26/2015

Yes

4/15/2015

Yes

No

215.555.1111

testing2@email.com

__TEST DATA 8

1241 Somewhere Street

Jersey Shore

PA

17740

Lycoming

8/19/2015

Yes

Yes

Yes

610.555.3333

testing4@email.com

__TEST DATA 14

1247 Somewhere Street

Dawson

PA

15428

Fayette

8/21/2015

Yes

__TEST DATA 40

1273 Somewhere Street

Somerset

PA

15501

Somerset

8/21/2015

Yes

<tbody>
</tbody>

Call Log Sheet

Call # <tbody> </tbody>	Client	Phone	Email	Caller	Date	Time	Notes <tbody> </tbody>
2	__TEST DATA 3 (Lookup List for Master list; which will be more than 10k)	215.555.1111 VLookup field pulls phone #	testing2@email.com VLookup field pulls email	Ben List lookup of callers	08/27/2015
5	__TEST DATA 1	215.555.1212	test@email.com	Adrienne	08/25/2015		asd ezsadf
7	__TEST DATA 3	215.555.1111	testing2@email.com	Jim	08/15/2015		adsfasd
8	__TEST DATA 4	212.555.3333	testing3@email.com	Adrienne	08/26/2015		called
9	__TEST DATA 4	212.555.3333	testing3@email.com	Ben	08/27/2015		asdfasdg

<tbody>
</tbody>

My area of focus is the lookup list for the Call Log sheet, which is currently pulling all 10k+ names - but we only need the names of those who have responded and who have replied "Yes" to Purchase question

 

[Aladin Akyurek]

Have a look at this formula approach: http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354.