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.
=XLOOKUP(E2, A2:A5, C2:C5, 0) How it works: This XLOOKUP formula is designed to efficiently retrieve specific data while gracefully handling missing values. `E2` is the 'lookup_value' (the Campaign ID you're searching for). `A2:A5` is the 'lookup_array' (the range where XLOOKUP will search for the Campaign ID). `C2:C5` is the 'return_array' (the range from which XLOOKUP will return the corresponding ROI). The crucial part for this scenario is the fourth argument, `0`. This is the 'if_not_found' argument, which tells XLOOKUP to return `0` instead of the default `#N/A` error if the `lookup_value` is not found in the `lookup_array`. This ensures your reports remain clean and error-free.
Data Setup
| Campaign ID | Campaign Name | ROI | Lookup ID | Result | |||
|---|---|---|---|---|---|---|---|
| C101 | Summer Sale | 0.15 | C101 | =XLOOKUP(E2 | A2:A5 | C2:C5 | 0) |
| C102 | Holiday Promo | 0.22 | C105 | =XLOOKUP(E3 | A2:A5 | C2:C5 | 0) |
| C103 | New Product Launch | 0.18 | |||||
| C104 | Email Blast | 0.10 |
Step-by-Step Guide
In your Excel sheet, identify the column containing your 'Campaign ID' (e.g., column A) and the column containing 'ROI' (e.g., column C).
Choose a cell where you want to enter the Campaign ID you're searching for (e.g., cell E2).
Select the cell where you want the ROI result to appear (e.g., cell F2).
Enter the XLOOKUP formula: `=XLOOKUP(E2, A2:A5, C2:C5, 0)`
Press Enter. The formula will return the ROI for the Campaign ID in E2, or '0' if the ID is not found in the list.
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.
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.
Count Leads from Specific Source
I need to count how many leads in my monthly report came specifically from 'Organic Search'.