Sometimes, one has a list of organizations' EINs and one would like to find corresponding data. In this example, we received a spreadsheet with several hundred EINs and were asked to supply NTEE codes for them.
The same technique could be used to obtain any type of data for any ad hoc list. For example, this approach could be used to see if we have data on a list of 200 organizations involved in advocacy work that you found on the web, a list of members of a particular association, or grantees of a particular foundation.
The attached spreadsheet guides you through the process of transforming the list into a comma- and quote-separated list, extracting the information into a spreadsheet, and an Excel function to match it back to the original list.
Two Excel tricks are used to make this work:
- The EINs are surrounded by single quotes and a comma using Excel's "concatenation" operator ("&")
- EINs are found using Excel's VLOOKUP function that searches an array (a rectangular section of cells) for a value in column 1 (EIN). If it finds the value, the function takes the user-specified column for the same row (e.g., the NTEE column).
NOTE: EIN IN ('01223344','023343543'...) is MUCH more efficient than EIN ='01223344' OR EIN = '023343543'. One test found that the former approach with a list of 73 EINs took 3 seconds to complete, while the latter took 150 seconds.
4/2003 ENHANCEMENT:
The following formula in Excel fixes 8-digit EINs where Excel has treated the EIN as a number and dropped the leading zero ("012345678" becomes 12345678). It also adds a "dummy" value of "xxxx" if an EIN is missing in order to avoid problems with the query.
="'"&IF(LEN(TRIM(C2))=8,"0","")&C2& IF(LEN(TRIM(C2))<4,"xxxx","")&"',"
Be sure to remove last comma from list of EINs.
2008 ENHANCEMENT:
You can now paste a list of EINs directly into the Freeform Filter box in the 990, BMF or Master List Search Tool. Each EIN should be on its own line, NO quotes are needed, and the FIRST LINE should say "EIN:"
EXAMPLE:
EINS:
123456789
987654321
...
KEYWORDS: "LIST OF EINS", "SPECIFIC EINS", "LIST OF ORGANIZATIONS", EXTRACTS