Map Lead Source to CRM Code
I have a list of lead sources like 'Facebook' and need to find their corresponding numeric code from a reference table.
=VLOOKUP(B2,D:E,2,FALSE) How it works: The VLOOKUP function searches for the 'Lead Source' (B2) in the first column of the specified table array (D:E). Once it finds an exact match, it returns the value from the second column (2) of that same row, which is the corresponding CRM Code. Using FALSE ensures an exact match, preventing incorrect code assignments for similar but not identical lead source names.
Data Setup
| Lead ID | Lead Source | CRM Code | Source | CRM Code |
|---|---|---|---|---|
| 101 | 1001 | |||
| 102 | Google Ads | Google Ads | 1002 | |
| 103 | 1003 | |||
| 104 | Email Campaign | Email Campaign | 1004 | |
| 105 | Organic Search | 1005 |
Step-by-Step Guide
Identify the lookup value: This is the 'Lead Source' in your main data (e.g., cell B2).
Define the table array: This is your reference table containing both the lead sources and their corresponding CRM codes (e.g., range D2:E6 or D:E for entire columns).
Specify the column index number: This is the column number in your table array that contains the value you want to return (e.g., 2, for the 'CRM Code' column in the D:E range).
Choose the range lookup type: For an exact match, use FALSE or 0.
Combine these into the VLOOKUP formula: =VLOOKUP(B2,D:E,2,FALSE).
Enter the formula in the first cell of your 'CRM Code' column (e.g., C2) and drag it down to apply to all lead sources.
Explore More
Create Comma-Separated Keyword Tags
I have a column of individual keywords and need to combine them into a single cell separated by commas for meta tags.
Extract File Extensions from URLs
I have a list of image URLs and need to extract the file type (e.g., .jpg, .png) from the end of the string. This is crucial for categorizing assets, checking file formats, or preparing data for content management systems.
Find Campaign ROI by ID (Return 0 if Missing)
I need to look up the ROI for a campaign ID, but if the ID isn't found, I want it to return '0' instead of an error.
Count Leads from Specific Source
I need to count how many leads in my monthly report came specifically from 'Organic Search'.