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:
[TABLE="width: 1551"]
<tbody>[TR]
[TD="align: left"]

Name

<tbody>
</tbody>

[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zipcode[/TD]
[TD]County[/TD]
[TD="align: left"]

Campaign Date

<tbody>
</tbody>

[/TD]
[TD]Purchased[/TD]
[TD="align: left"]

If yes, When?

<tbody>
</tbody>

[/TD]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Phone 1[/TD]
[TD="align: left"]

Phone 2

<tbody>
</tbody>

[/TD]
[TD]Email[/TD]
[TD]Date Rcvd[/TD]
[TD]No Contact[/TD]
[/TR]
[TR]
[TD]__TEST DATA 1[/TD]
[TD]1234 Somewhere Street[/TD]
[TD]Glenolden[/TD]
[TD]PA[/TD]
[TD]19036[/TD]
[TD]Delaware[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]215.555.1212[/TD]
[TD]610.555.1212[/TD]
[TD]test@email.com[/TD]
[TD]8/25/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]__TEST DATA 3[/TD]
[TD]1236 Somewhere Street[/TD]
[TD]Perkasie[/TD]
[TD]PA[/TD]
[TD]18944[/TD]
[TD]Bucks[/TD]
[TD="align: right"]8/26/2015[/TD]
[TD]Yes[/TD]
[TD]4/15/2015[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]215.555.1111[/TD]
[TD][/TD]
[TD]testing2@email.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]__TEST DATA 8[/TD]
[TD]1241 Somewhere Street[/TD]
[TD]Jersey Shore[/TD]
[TD]PA[/TD]
[TD]17740[/TD]
[TD]Lycoming[/TD]
[TD="align: right"]8/19/2015[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]610.555.3333[/TD]
[TD][/TD]
[TD]testing4@email.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]__TEST DATA 14[/TD]
[TD]1247 Somewhere Street[/TD]
[TD]Dawson[/TD]
[TD]PA[/TD]
[TD]15428[/TD]
[TD]Fayette[/TD]
[TD="align: right"]8/21/2015[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]__TEST DATA 40[/TD]
[TD]1273 Somewhere Street[/TD]
[TD]Somerset[/TD]
[TD]PA[/TD]
[TD]15501[/TD]
[TD]Somerset[/TD]
[TD="align: right"]8/21/2015[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Call Log Sheet
[TABLE="width: 1114"]
<tbody>[TR]
[TD="align: left"]

Call #

<tbody>
</tbody>

[/TD]
[TD]Client[/TD]
[TD]Phone[/TD]
[TD]Email[/TD]
[TD]Caller[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD="align: left"]

Notes

<tbody>
</tbody>

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


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.