Excel: Look up Data in Columns

Use this action to search a column in an Excel XLSX or CSV file for matching data and return data from other columns in the same row.

This action makes it easy to find data in a table by row, like looking up the price of product by the product name. If multiple rows match the Search term, only the first matching row is returned. To filter a table and return multiple rows based on a criteria, try the Excel: Apply Filters or Excel: Run SQL query on Excel file and return Excel file actions.

Use Case

Example

• Olivia connected her POS systems to a Workflow, and now has data on each transaction across her stores. She can use the Excel: Look up Data in Columns action to look up individual transactions based on a unique transaction ID. She configures the action to return the store location, sale price, and items sold for that specific transaction. She plans to use this in a reporting Workflow that sends alerts depending on a transaction’s sale price.

How to configure this action

This action searches a Lookup column for a Search term. If the action finds a match, it returns data from that row based on the Columns to return.

For example, to look up the Quantity and Vendor ID of an Apple from a data table, we need to search the Fruit column for the Apple row and return data from that row for the Quantity and Vendor ID columns.

For example, to find the price and quantity of an Apple from the table above:

1. Set the Lookup column to 1, the first column.
2. Set the Search term to Apple, the column value we’re looking for.
3. Set the Columns to return to 3,4, the Quantity and Vendor ID column.
4. The output of this action would be two fields; the first field has the value 5 and the second field has the value ABC.

Fields for this action

• Spreadsheet file

  • This is the Excel file you want to filter. Use a field reference to a file uploaded in a prior task or instance

• Sheet

  • The worksheet name or number to look up data in. Enter a number to select by worksheet order. If the sheet does not exist, it will be created.
  • By default, Excel sheet names are “Sheet1”, “Sheet2”, etc. Enter a number to select by worksheet order, the first sheet starts at 1.

• Header row number

  • Enter the row number that contains the names of the columns. Leave blank if the file does not have a header row.
  • ✅   Heads-up: If blank, columns can only be referenced by Excel or column order reference style.

• Column Reference Type

  • Enter the column reference type to use during configuration; Column Name, Excel, or Column Order. Subsequent fields must be configured according to the column reference type you choose.

  Column name	If a header row is set, you can reference columns directly by their name in {{column-name}} format.
  Excel	You can reference columns by their Excel letter, such as A, B, C.
  Column order	You can reference columns by their order, such as 1, 2, 3.

  • If Header row number is not specified, only Excel or Column Order references can be used.

• Lookup column

  • Choose the column to search through using the Column Name, Excel Style reference, or Column order. You must reference the column based on the column reference type specified above.
  • For example, if the search term is Apple, the action will search this column for that term.

  💡   Tip: If you’re only searching for matching data in one column, enter the same column number for lookup column and Columns to return.

• Search term

  • Enter the term to search the lookup column name for. For example. if the lookup column contains fruit names, enter the fruit to search for here.
    • By default, the action performs an exact match using the term. To use REGEX, set the Advanced search type field to REGEX and enter the expression in this field.

• Columns to return

  • Choose which columns the action returns from the matched row using the Column Name, Excel Style reference, or Column order. You must reference the column based on the column reference type specified above.

    💡   Tip: If you are searching for matching data in several columns, enter the other column numbers in the 2nd column, 3rd column, 4th column, and 5th column fields.

• Advanced search type

  • Enter the type of search.
    • Leave empty to perform an exact match search on the column that uses the Search term field value.
    • Enter REGEX to perform a REGEX search on the column that uses the Search term field value as the regular expression.

• Retain formatting

  • By default, the action will extract the underlying raw data from cells. You can choose whether you extract the displayed value, or the raw data. For example, a finance formatted cell displays $(12,345.67), but the raw value is -12,345.67.
    • Select True to extract the formatted display value. For example, a finance-formatted cell that contains $(12,345.67).
    • Select False to extract the raw cell data. For example, -12,345.67.
  • Dates and times will always be imported in ISO format YYYY-MM-DD and HH:MM:SS, respectively. Date times will respect the value of the “Retain formatting” parameter.

• Output field prefix

  • To help keep output fields organized, choose a prefix that will be added to the beginning of each output field. The name you specify will become the output field prefix for the field.

What will this output?

This action will output the a field for each of the columns to return.

Output fields for this action

• Columns to return (2nd, 3rd, etc.)

  • Each output field will store the result from the column defined during configuration. If no match is found, or there is no data in the row, the field will be blank.

Get help with a problem or question

If something’s not working as expected, or you’re looking for suggestions, check through the options below.