Using SQL “IN” Function in Excel

In Excel, the SQL IN function isn’t directly available as it is in SQL databases, but you can simulate the behavior of SQL’s IN function using Excel formulas like IF, MATCH, or COUNTIF in combination with other Excel features.

Here’s how you can replicate the SQL IN functionality in Excel:

1. Using MATCH Function (with ISNUMBER)

You can use MATCH combined with ISNUMBER to check if a value is in a list of values, similar to how IN works in SQL.

Syntax:

=IF(ISNUMBER(MATCH(value, range, 0)), "Yes", "No")

• value: The value you want to check if it exists in the list.
• range: The range where the values will be checked.
• MATCH(value, range, 0): Returns a number if a match is found, and #N/A if no match is found.
• ISNUMBER(): Converts the result into TRUE if a match is found (because MATCH will return a number) or FALSE if no match is found.

Example:

Let’s say you have a list of products in column A (from A2:A10) and you want to check if a product in B2 exists in that list.

=IF(ISNUMBER(MATCH(B2, A2:A10, 0)), "In List", "Not In List")

This formula will check if the value in B2 is present in the range A2:A10 and return “In List” if it is, or “Not In List” if it’s not.

2. Using COUNTIF Function

COUNTIF counts how many times a specific value appears in a range. You can use it to replicate the IN function by checking if the count is greater than zero.

Syntax:

=IF(COUNTIF(range, value) > 0, "Yes", "No")

• range: The range where the values will be checked.
• value: The value you want to check if it exists in the range.
• COUNTIF(range, value): Counts occurrences of value in the range.
• If the result of COUNTIF is greater than 0, it means the value is found in the range.

Example:

Check if the value in cell B2 exists in the range A2:A10:

=IF(COUNTIF(A2:A10, B2) > 0, "In List", "Not In List")

This formula will return “In List” if the value in B2 is found in A2:A10, otherwise, it will return “Not In List”.

3. Using IF with Multiple Conditions (Simulating Multiple Values in IN)

If you’re checking whether a value matches multiple possible values, you can use OR with IF.

Example:

Let’s say you want to check if the value in B2 matches any of the following three possible values: “Apple”, “Banana”, or “Orange”. You can do this using:

=IF(OR(B2="Apple", B2="Banana", B2="Orange"), "Match", "No Match")

This simulates the IN function from SQL by explicitly listing the values to check against.