Excel formula help

Renee Schweke
Occasional Contributor

I need a formula to help me look at a column of email addresses. 

There are many agencies using the same email extension.

For example:






Let's say that NYPD's agency code is 1

FDNY is 2

Schools is 3

and Mayor is 4.


I want to look at column C, which has the email addresses.  Let's say there are 250 email addresses in total. 


Each agency has several employees.  In column D, I would like to display the agency code of the agency. 


This way, I can then group all the email addresses with code 1, all the emails with code 2, etc.


Thank you in advance.


2 Replies

Hi Renne,


You need to a lookup table to get the code name form it.

Once you have such a table, you can use a lookup formula such as VLOOKUP to get what you want.


Here's an example


The formula in cell D2:





Hope that helps

If the only goal is to group emails by agencies when Pivot Table works - extract agencies names and pivot then.