MATCH or Macro? Populate Email Address Based On Data in Column

[kraamerica]

I have a sheet where the data changes daily based on certain parameters (overdue dates). I need a way to pull the email address associated with the data in the specific column (cell) to populate in another column in the same row so that I can send a specific email to specific people instead of a blanket to over 180 locations (and obviously have to go through and try to find their email address from memory or one at a time).

I can put all the email data in one sheet and just copy the daily sheet I receive if that's easiest. I'm not sure what data I can show or send to this forum for an example. Is a screenshot the best way? or the excel sheet itself?

Column A is an associated code, B is the Name of the location that I need the email address mapped to (or vice versa). C-G contain dates/reference numbers.

 

[rhratterman15]

A screenshot would be helpful.

 

[kraamerica]

So I would like to try and use a match or macro so that when the data in column B is copy/pasted/entered, that it's corresponding email address automatically populates Column I

 

[rhratterman15]

Can you share a sample of the sheet where the emails are stored? What's the sheet name?

 

[kraamerica]

Workbook of the screenshot above is searchdisplayinventory. The email addresses are just kept in a contact sheet right now titled 2020 April MI_OH_NY Dealers District 6102 (but can be changed to whatever as I was hoping to just have a master template of the searchdisplayinventory where I would copy current data over to if I need to keep and update the contacts on a separate sheet in that master template (which can also be called whatever). Here is the contact sheet example:

 

[rhratterman15]

Not sure if this is what you are looking for but maybe try this:

=INDEX('2020 April MI_OH__NY Dealers'!$B$2:$K$1000,MATCH(A2,'2020 April MI_OH__NY Dealers'!$A$2:$A$1000,0),MATCH($I$1,'2020 April MI_OH__NY Dealers'!$B$1:$Z$1,0))

$I$1 value = EMAIL
A2 value = Dealer Number

See attached photos.

 

[kraamerica]

OK thank you. So, long story short, I am referencing the workbook with the contacts and my master template will contain that reference =INDEX('2020 April MI_OH__NY Dealers'!$B$2:$K$1000,MATCH(A2,'2020 April MI_OH__NY Dealers'!$A$2:$A$1000,0),MATCH($I$1,'2020 April MI_OH__NY Dealers'!$B$1:$Z$1,0))

As long as the contact sheet maintains correct email address WITH the dealer number, it will always return what is populated in those 2 columns?

And if I created a master contact list I just change the reference workbook and sheet name in the formula above?

 

[kraamerica]

Not sure if this is what you are looking for but maybe try this:

=INDEX('2020 April MI_OH__NY Dealers'!$B$2:$K$1000,MATCH(A2,'2020 April MI_OH__NY Dealers'!$A$2:$A$1000,0),MATCH($I$1,'2020 April MI_OH__NY Dealers'!$B$1:$Z$1,0))

$I$1 value = EMAIL
A2 value = Dealer Number

See attached photos.

FREAK YEAH! Real test will be tomorrow, but I get the gist and the rest this lowly wannabe can handle (maybe!) . Thanks so much!

 

[alansidman]

An alternative solution would be to bring both tables into Power Query/Get and Transform and merge the two tables joined on the DLR Name and Name fields to get the email address. If you would like to see this solution, then instead of pictures (I cannot manipulate pictures), load each sample table to this site using XL2BB.

 

[rhratterman15]

FREAK YEAH! Real test will be tomorrow, but I get the gist and the rest this lowly wannabe can handle (maybe!) . Thanks so much!

Hope it works for you. Good luck.