Lookup

The Lookup function is a feature that allows you to search for a specific value within a data range and return a corresponding value from a different column within the same range. This function serves as an advanced alternative to Excel's VLOOKUP, offering enhanced flexibility. Unlike VLOOKUP, which can only return the first match it finds, this Lookup function provides options to retrieve all matching entries, the first matching entry, or the last matching entry.

See it in Action

Requirements

Data

• Target Data

  • Data Range: Choose the range of cells where you want the function to operate. This is where the function will look for the value you're searching for.

  • Column to Search: Specify the column within your data range that contains the values you want to search.

  • Column to Return: Indicate the column from which to retrieve the corresponding value once the search key is found.

• Search Key Range:

  • Data Range: This specifies the range within which your search keys are located, distinct from the main target data range. While the main target data is where the function searches for matching values, the Data Range for the Search Key contains the values you're using as criteria for the search.

  • Columns Included?: Check this option if your selected range includes the first row as column headers. This ensures that the first row is recognized as column names and excluded from the processing.

  • Lookup Pattern

    • All Matches: Select this option if you want the function to return all matching entries. Useful for identifying multiple records that meet the search criteria.

    • Only First Match: Choose this to return only the first matching entry. Ideal for situations where only the initial match is relevant. This is same as the Vlookup formula in Excel.

    • Only Last Match: Opt for this to find only the last occurrence of the search key. This is useful when the most recent entry is of primary interest.

  • Delimiter (Only when selecting All Matches): Enter the character or string used to merge all the matches.

Fill empty cells with

• Keep Empty: Leave cells that are empty as is, without adding any text.

• Forward Fill: Copy the value from the nearest non-empty cell above into empty cells before adding the specified letters or text.

• Backward Fill: Copy the value from the nearest non-empty cell below into empty cells before adding the specified letters or text.

• -: Fill empty cells with a letter "-".

• 0: Fill empty cells with a letter "0".

• Custom: Allows for a custom value or text to be inserted into empty cells prior to adding the specified letters.

Result Destination

Specify the starting cell where the results will be placed.