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.
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.
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")
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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agoThanks so much, this worked perfectly for what I needed!
- To post as a guest, your comment is unpublished.· 2 years agoHello there,
Kindly help me with function/command that will arrange same vaue in the two columns given below:
- To post as a guest, your comment is unpublished.· 4 years agoI 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.
- To post as a guest, your comment is unpublished.· 3 years agoSatir, late message but I just did the exact job that you are tasked with.
=IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)), "False", "True").
=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").
[b](B1 AND B2 BEING THE CELLS WITH VALUES YOU WANT IT TO DISPLAY INSTEAD OF YES/NO)[/b]
- To post as a guest, your comment is unpublished.· 1 years agoI got the simpler one, use this ,, =IFERROR(VLOOKUP(A2,$D$2:$D$185,1,FALSE)),"Not available"
- To post as a guest, your comment is unpublished.· 11 months agoThis formula worked for my situation. Thank you!
- To post as a guest, your comment is unpublished.· 4 years agocan 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.