Site icon Best Excel Tutorial

Vlookup That Returns True or False

The VLOOKUP function in Excel can be used to return a value from a table based on a lookup value. However, sometimes you might want to return a simple True or False result based on whether or not a value exists in a table.

In this article, you learn 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.

To demonstrate how this can be done, let’s consider a simple example. Suppose you have a list of items and their prices in a table, and you want to determine if the price of a specific item is greater than a certain value. You can use the VLOOKUP function in combination with the IF function to accomplish this.

The syntax for the VLOOKUP function is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where:

The syntax for the IF function is as follows:

=IF(logical_test, [value_if_true], [value_if_false])

Where:

To use the VLOOKUP function to return either “TRUE” or “FALSE”, you need to combine it with the IF function. The following formula demonstrates how this can be done:

ISNA formula

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))

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.

Of course this vlookup based formula can be used both for such basic data table and for advanced business intelligence dashboard.

Exit mobile version