Skip to main content

How to vlookup value and return true or false / yes or no in Excel?

In many cases, you may need to look for values in a column and just return true or false (yes or no) if the value was found or not in another column. In this article, we are going to show you methods to achieve it.

Vlookupand return true or false / yes or no with formula
Highlight values in a column if they are found in another column with an amazing tool

More tutorials for VLOOKUP...

Vlookupand return true or false / yes or no with formula

Supposing you have a list of data in range A2:A18 as shown in following screenshot. To search the values in A2:A18 according to the value in D2:D4 and display the result True or false / Yes or No, please do as follows.

1. Select a blank cell to output the result. Here I select B2.

2. Enter the below formula into it and press the Enter key.

=IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)), "No", "Yes")

3. Select the result cell, and then drag the Fill Handle to apply the formula to other cells (In this case, I drag the Fill Handle down until it reaches B18). See screenshot:

Note: For returning True or False, please replace the “Yes” and “No” in the formula with “True” and “False”:
=IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)), "False", "True")

Highlight values in a column if they are found in another column with an amazing tool

If you want to stand out values (such as highlight them with background color) in a column if they are found in another column, here highly recommended the Select Same & Different Cells utility of Kutools for Excel. With this utility, you can easily achieve it by clicks only. as the below demo shown. Download Kutools for Excel now! (30-day free trail)

Let's see how to apply this feature to highlight values in a column if they are found in another column.

1. After installing Kutools for Excel, click Kutools > Select > Select Same & Different Cells to enable the utility.

2. In the Select Same & Different Cells dialog box, please configure as follows.

  • 2.1) In the Find values in box, select the range which you will highlight values in;
  • 2.2) In the According to box, select the range you will highlight values based on;
  • 2.3) In the Based on section, check the Single cell option;
  • 2.4) In the Find section, choose the Same values option;
  • 2.5) In the Processing of results section, check the Fill backcolor or Fill font color as you need, specify a highlight color;
  • 2.6) Click the OK button. See screenshot:

Then, if values in range A2:A18 was found in C2:C4, they will be highlighted and selected immediately as the below screenshot shown.

  If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

related articles

Vlookup values across multiple worksheets
You can apply the vlookup function to return the matching values in a table of a worksheet. However, if you need to vlookup value across multiple worksheets, how can you do? This article provides detailed steps to help you easily solve the problem.

Vlookup and return matched values in multiple columns
Normally, applying the Vlookup function can only return the matched value from one column. Sometimes, you may need to extract matched values from multiple columns based on the criteria. Here is the solution for you.

Vlookup to return multiple values in one cell
Normally, when applying the VLOOKUP function, if there are multiple values that match the criteria, you can only get the result of the first one. If you want to return all matched results and display them all in a single cell, how can you achieve?

Vlookup and return entire row of a matched value
Normally, using the vlookup function can only return a result from a certain column in the same row. This article is going to show you how to return the whole row of data based on specific criteria.

Backwards Vlookup or in reverse order
In general, the VLOOKUP function searches values from left to right in the array table, and it requires the lookup value must stay in the left side of target value. But, sometimes you may know the target value and want to find out the lookup value in reverse. Therefore, you need to vlookup backwards in Excel. There are several ways in this article to deal with this problem easily!

More tutorials for VLOOKUP...

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have four criteria, Heated, RF enabled, Carbon Filters, 3 Storey.

i have created a table showing model numbers with corresponding YES & NO to the above criteria. 

i want to create a way to bring back a singular result when i choose relative YES & NO for each criteria.
Can this be done? 
This comment was minimized by the moderator on the site
Thanks so much, this worked perfectly for what I needed!
This comment was minimized by the moderator on the site
Hello there,
Kindly help me with function/command that will arrange same vaue in the two columns given below:
This comment was minimized by the moderator on the site
I need help using vlookup with if function for yes or no but this time the result we be as value not as Yes / NO or True or False, the oppsite of the above example.
This comment was minimized by the moderator on the site
Satir, late message but I just did the exact job that you are tasked with. Original Version =IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)), "False", "True"). My Version: =IF(A2="","",(IF(ISNA(HLOOKUP(A2,'1. Round One'!$C$2:$Q$2,1,FALSE)),"",'1. Round One'!$H$5&" "&'1. Round One'!$H$6&" "&'1. Round One'!$H$7))) The difference is mine will return 3 values that are related to the value I just looked up.. aswell as it wont display anything unless the value was found.. to simply it for your use, ive provided one below. =IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)), "B1", "B2"). (B1 AND B2 BEING THE CELLS WITH VALUES YOU WANT IT TO DISPLAY INSTEAD OF YES/NO)
This comment was minimized by the moderator on the site
I got the simpler one, use this ,, =IFERROR(VLOOKUP(A2,$D$2:$D$185,1,FALSE)),"Not available"
This comment was minimized by the moderator on the site
This formula worked for my situation. Thank you!
This comment was minimized by the moderator on the site
can i use Yes / NO in vlookup to return a result to number or value. all the exmple gives result of Yes / NO or True/ False i need oppsite to it.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations