Confused and Need Help w/Vlookup

[oball23]

Dear Excel Wizards-

I have a list of about 105,000 email addresses in Sheet1, and in Sheet2 I sent a opt-in/opt out email to 15,000 contacts.

Now I am trying to separate out the action each email took IF they were the lucky 15,000 list- bounce, clicks, unsubscribe, opened, etc. using a vlookup formula: =VLOOKUP($A$2,Sheet2!$A$2:$F$11,2,FALSE) for each category aforementioned per email address however I am having difficulty figuring out this:

1. How can I use an absolute reference in each row without manually entering the $? For example, $A$2 wont drag so I have to change the next row to manually to $A$3.
2. In the vlookup formula, how I get the formula to column reference the next column when copying the formula? For example, column 2-"opens", to column 3- "clicks" and so forth thru column-6
3. Some columns have the email addressee selecting two options- unsubscribing AND opening or whatever, how do I get both options to appear?
4. The list is so large that it freezes often, how can I fix this problem?

Thanks again geniuses! If someone can show me a formula, I can apply it to the rest.

 

[AliGW]

1. Change $A$2 to $A2 so that the row changes as you drag down.

2. Change the column reference 2 to column()-1.

 

[arthurbr]

Cross post without links at excelguru.ca

 

[oball23]

Thank you very much! And I am sorry about the cross post and will delete.

Any idea how I can get two queries to appear on the same row? For instance one contact unsubscribed and opened but only one or the other is appearing.

 

[Peter_SSs]

Thank you very much! And I am sorry about the cross post and will delete.

Cross-posting is allowed in this forum, but the etiquette of it is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

2. Change the column reference 2 to column()-1.

Two comments about that:
i) Assuming the original formula was in cell B2, this will return the looked up value from column 1 of the lookup table, not column 2 per the original formula. The -1 would need to be omitted.

ii) I think the use of the COLUMN() function for this type of thing is risky. If any column(s) are subsequently inserted to the left of the formula, it will begin returning incorrect results without warning.
A safer alternative for the B2 formula would be

=VLOOKUP($A2,Sheet2!$A$2:$F$11,COLUMNS($B2:B2)+1,FALSE)

Any idea how I can get two queries to appear on the same row? For instance one contact unsubscribed and opened but only one or the other is appearing.

I'm not sure what you are asking here. Could you provide a small set of dummy data and the expected result(s) and explain again in relation to that sample data?
See Attachments for good ways to provide sample data.

 

[Marcelo Branco]

Two comments about that:
i) Assuming the original formula was in cell B2, this will return the looked up value from column 1 of the lookup table, not column 2 per the original formula. The -1 would need to be omitted.

ii) I think the use of the COLUMN() function for this type of thing is risky. If any column(s) are subsequently inserted to the left of the formula, it will begin returning incorrect results without warning.
A safer alternative for the B2 formula would be

=VLOOKUP($A2,Sheet2!$A$2:$F$11,COLUMNS($B2:B2)+1,FALSE)

Agree 100%. I think it's important to emphasize the use of Columns($B2:B2) instead of Column()

This issue (risk) was already mentioned yesterday in another thread
http://www.mrexcel.com/forum/excel-...ying-columns-vlookups-easily.html#post4590094

M.

 

[FDibbins]

Also agree. IO got into the (bad) habit of using just COLUMN(), and am trying hard to break that habit