'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find an Email Match in Excel and Return the Adjacent Value
Question details
The user needs an Excel formula to check if an email address exists in a specific lookup column and output a corresponding value in an adjacent cell on the same row if a match is found.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two lists of data, specifically verifying if an email address from one list is present in another, and outputting data based on that match.
- Observed behavior
- Requires a reliable formula to scan a referenced range for an email address, returning a designated value for true matches and leaving the cell blank if there is no match.
Ensure your lookup list is locked using absolute references (like $H$2:$H$100) so the range does not shift when you drag the formula down. Also, check that your email data does not contain hidden leading or trailing spaces.
Use IF and COUNTIF Formulas to Find the Match
This method counts the occurrences of an email in your reference list and uses an IF statement to return the designated value when the count is greater than zero.
The COUNTIF function scans your designated lookup list for the target email. By wrapping this function inside an IF statement, you instruct Excel to output the email (or any other value you specify) if a match is found, and return an empty cell otherwise.
Click on the cell in the adjacent column where you want the result to appear (for example, cell G2).
Type the formula =IF(COUNTIF($H$2:$H$100,F2)>0,F2,"") into the formula bar. In this example, F2 is the cell containing the email you are looking for, and $H$2:$H$100 is your main lookup list.
Press Enter to execute the formula and view the result for the first row.
Click the small square at the bottom-right corner of cell G2 and drag it down your column to apply the formula to the rest of the email addresses.
Use VLOOKUP as an Alternative
VLOOKUP is another popular choice if you need to pull corresponding data from a completely different column rather than just verifying the email's existence.
Manage Data and Formulas Effortlessly with WPS Office
WPS Spreadsheet provides comprehensive support for all standard lookup and logical functions, making it incredibly easy to cross-reference data and find email matches without performance lag.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your email lists.
- 2. Input the lookup formula: Select the target cell, type the =IF(COUNTIF(...)) formula, and press Enter.
- 3. Batch apply the formula: Double-click the fill handle in the bottom-right corner of the cell to instantly apply the formula to the entire column.
Frequently Asked Questions
Why does my formula return a blank cell even when I can see the email in both lists?
This is typically caused by hidden leading or trailing spaces in one of the cells. You can use the TRIM function or the 'Find and Replace' tool to remove extra spaces from both columns so they match exactly.
Can I use XLOOKUP for this task?
Yes, if your version of Excel or WPS Office supports XLOOKUP, it provides a cleaner syntax. You can use =XLOOKUP(F2, H2:H100, H2:H100, "") to find the exact match and return it, or output a blank cell if not found.
How do I ignore case sensitivity when looking up emails?
The COUNTIF and VLOOKUP functions are not case-sensitive by default. Searching for 'test@email.com' will successfully match 'Test@Email.com' without requiring any modifications to the formula.
