Clay Icon

Help Needed: Lookup Formula Error in Counting Jobs by Company

Β·
Β·

Hey guys - I have a lookup formula error Use case is simple. I have a list of companies in one table and a list of jobs in another. The jobs table has a column for role group I want to add columns to the companies table, using domain as a reference, to count the total number of jobs in one column and the total number engineering jobs in another column, for that company Any help would be super-appreciated?! https://www.loom.com/share/71fa425f406f47139aaa95e90bee9eda?sid=693cedb5-c9db-4c68-8fbf-554737f86c15

  • Avatar of Channeled
    Channeled
    APP
    Β·
    Β·

    To fix your lookup formula error and count jobs for companies, you can use the "Lookup Multiple Rows" feature. Here's how: 1. In your companies table, add a new column for job counts. 2. Use "Lookup Multiple Rows" to search the jobs table, matching company domains. 3. Count the total jobs and engineering jobs separately. 4. For engineering jobs, add a condition to check the "role group" column. If you're still seeing an "Error evaluating formula" message, try refreshing your browser with Cmd/Ctrl + Shift + R. For more detailed instructions on setting up the lookup, check our documentation on the "Lookup Multiple Rows" feature.

  • Avatar of Richard R.
    Richard R.
    Β·
    Β·

    how do you add conditions to the lookup?

  • Avatar of Channeled
    Channeled
    APP
    Β·
    Β·

    To add conditions to the "Lookup Multiple Rows" feature in Clay, you can use the Filter Operator and Row Value inputs. Here's how: 1. Select the "Table to Search" where you want to look up rows. 2. Choose the "Target Column" to match against. 3. Set the "Filter Operator" to define how you want to compare values (e.g., equals, contains, etc.). 4. Specify the "Row Value" to search for. 5. Optionally, set a "Limit" to control the maximum number of rows returned. For more complex conditions, you can use the "Only run if" option in the Run Settings. This allows you to create conditional formulas that determine when the lookup should be performed, helping you save credits and run enrichments more efficiently.

  • Avatar of Richard R.
    Richard R.
    Β·
    Β·

    no, I need to filter on company_domain and on role group to get the jobs for a specific company that are in the filtered role group

  • Avatar of Bo (.
    Bo (.
    Β·
    Β·

    Hi Rich, I just checked your table but couldn't find this workbook. To solve your issue, you need to reference the lookup first if you want to extract values from it. For example, you would need to write your prompt like this: /lookup return the number of times where [title] is mentioned Currently, it's not clear what specific data you're trying to reference in your formula. Make sure to specify the lookup source before writing the filtering condition.

    (48 kB)
    Image
  • Avatar of Channeled
    Channeled
    APP
    Β·
    Β·