The original example can be found at TechOnTheNet.com.
Question: I have a list of #s in column A (lets say 1-20). There is a master list in another column that may not include some of the column A #s. I want a formula in column B to say (if A1 exists in the master list, then “Yes”, “No”). Is this possible?
Answer: This can be done with a formula that utilizes a combination of the VLookup function, IF function, and ISNA function.
Based on the spreadsheet above:
=IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)),”No”,”Yes”) would return “No”
=IF(ISNA(VLOOKUP(A5,$D$2:$D$185,1,FALSE)),”No”,”Yes”) would return “Yes”
First, you need to enter a FALSE in the last parameter of the VLookup function. This will ensure that the VLookup will test for an exact match.
If the VLookup function does not find an exact match, it will return the #N/A error. By using the IF and ISNA functions, you can return a “Yes” value if an exact match is found. Otherwise, a “No” value is returned.
Recent Comments