In this article you will teach yourself how to create a vlookup formula which returns just true of false. You may need it to check if you have some data missing.
It is possible to create a vlookup formula which returns only boolean values - true or false.
Let's take a look at this example.
I created such a table.
I want to check if I have some data missing. For example I just type a customer's name to check if I need to add it to my data table. In such example I need only true or false answer.
To check it I created such a formula: =ISNA(VLOOKUP($B$7,B2:B5,1,FALSE))
- ISNA - to not get an #N/A! error
- $B$7 - is an absolute reference to a value I know (customer's name)
- B2:B5 - data range 1 - first column
- FALSE - because I need an exact match
IF and ISNA formula
Another examples would be to check if the guest is invited to the particular party.
I will use If function with ISNA as previously.
The formula is: =IF(ISNA(VLOOKUP(A4,$D$2:$D$5,1,FALSE)), "False", "True")
As you can see the formula recognized that one of guest is already invited to the party.
The formula used is preventing me before N/A! errors. Usage of IF function will return True of False as I needed.
Other conditions you may want to use this formula:
- to check if the client should get a discount
- to check if the student passsed get exam
- to check if the cleck should get a bonus
Of course this vlookup based formula can be used both for such basic data table and for advanced business intelligence dashboard.