Excel Formula for Conditional Lookup

Formula

The following formula can be used in the first spreadsheet to achieve the specified functionality:

=IF(A1<>"", VLOOKUP(A1, 'Tab Two'!A:A, 1, FALSE), "")

Explanation

• IF Function:

  • The IF function checks whether the cell in Column A (e.g., A1) is not blank
  • Syntax: IF(condition, value_if_true, value_if_false)
    • condition: A1<>"" checks if A1 is not blank.
    • value_if_true: If true, it performs a VLOOKUP.
    • value_if_false: If false, it returns an empty string "".

• VLOOKUP Function:

  • The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from a specified column.
  • Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
    • lookup_value: A1 (the value to look up from the first tab).
    • table_array: 'Tab Two'!A:A specifies that the function should search in the first column of "Tab Two".
    • col_index_num: 1, indicating that the return value should be in the first column of the specified range.
    • range_lookup: FALSE indicates that an exact match is required.

Practical Example

Scenario:

• Tab One contains:

  A
  Apple
  Banana
  Cherry

• Tab Two contains:

  A
  Banana
  Cherry
  Apple

Implementation:

1. In Tab One, starting from cell B1, enter the formula:

   =IF(A1<>"", VLOOKUP(A1, 'Tab Two'!A:A, 1, FALSE), "")

2. Drag the formula down to cover all relevant cells in Column B.

Result:

• After applying the formula, Column B will display:

  A	B
  Apple	Apple
  Banana	Banana
  Cherry	Cherry

Conclusion

This formula effectively allows for a conditional lookup based on the presence of values in one column, facilitating data retrieval from another worksheet within the same workbook. For further mastery of Excel formulas and data analysis skills, consider exploring additional resources on the Enterprise DNA Platform.