How to Use Vlookup to Check if Value Exists

The vlookup function can easily help you determine if a value that is in one column is in another.

Data preparation

This can be useful if the list is very long.

You should have data in one column and have a duplicate in another.

Note: You can choose to have another kind of data in the columns.

Vlookup formula

Click on an empty column (1), and type in =IF(ISERROR(VLOOKUP(B2,$D$2:$D$16,1,False)),False,True) (2), and then click Enter on the keyboard.

Note: This is to know if the value in B2 exists in another column.

Using the VLOOKUP Function to Find Multiple Values

In addition to checking if a single value exists in another column, the VLOOKUP function can also be used to find multiple values. This can be done by using the wildcard character (*) in the value argument of the VLOOKUP formula.

The wildcard character () can be used to represent any number of characters. For example, the following formula would check if any of the values in the range A2:A10 match the value “123”:

=VLOOKUP(“*123*”, A2:A10, 2, False)

This formula would return the value in the corresponding cell in the range B2:B10 if any of the values in the range A2:A10 match the value “123*.”

The VLOOKUP function can also be used to find multiple values by using the MATCH function. The MATCH function is used to find the position of a value in a range.

The following formula would find the position of the first occurrence of the value “12345” in the range A2:A10:

=MATCH(“12345”, A2:A10, 0)

The MATCH function returns an integer value that represents the position of the value in the range. This value can then be used as the column_index argument of the VLOOKUP function to find the corresponding value in another range.

For example, the following formula would check if the value “12345” exists in the range A2:A10 and return the value in the corresponding cell in the range B2:B10 if the value is found:

=VLOOKUP(12345, A2:A10, MATCH(“12345”, A2:A10, 0), False)

This formula would return the value in the corresponding cell in the range B2:B10 if the value “12345” exists in the range A2:A10.

How to Check if a Value Exists in Another Sheet

The VLOOKUP function can also be used to check if a value exists in another sheet in Excel. To do this, you will need to use the sheet_name argument in the VLOOKUP formula.

The sheet_name argument specifies the name of the sheet that contains the values that you want to check for. For example, the following formula would check if the value “12345” exists in the range A2:A10 of the sheet named “Sheet2”:

=VLOOKUP(12345, Sheet2!A2:A10, 2, False)

The ! symbol is used to separate the sheet name from the range of cells.

How to Check if a Value Exists in List

Suppose you have a list of values in column A (A2:A10) and you want to check if a specific value, say “X,” exists in the list. Here are the steps:

1. 1. In an empty cell, enter the VLOOKUP formula. Let’s assume you want to check if “X” exists in the list in cell A2:

=VLOOKUP("X", A2:A10, 1, FALSE)

1. 1. • "X" is the value you want to check.
      • A2:A10 is the range that contains the list of values.
      • 1 is the column index from which you want to retrieve data (the first column in this case).
      • FALSE specifies an exact match.
   2. Press Enter.
   3. If “X” exists in the list, the formula will return the value “X.”
   4. If “X” does not exist in the list, the formula will return the #N/A error.
   5. To check the result in a more user-friendly way, you can wrap the VLOOKUP formula in an IFERROR function:

=IF(ISNUMBER(MATCH("X", A2:A10, 0)), "Exists", "Does Not Exist")

• • MATCH("X", A2:A10, 0) checks if “X” exists in the list (returns a number if found, or #N/A if not found).
  • ISNUMBER(...) checks if the result is a number (i.e., “X” exists).
  • The IFERROR function returns “Exists” if “X” exists and “Does Not Exist” if it does not.

Press Enter, and the cell will display either “Exists” or “Does Not Exist” based on whether “X” is found in the list.

This method allows you to quickly check if a value exists in a list using the VLOOKUP function and provides a clear result based on your criteria.