Skip to main content

Lookup DataTable

Searches for a value in a specified DataTable column and returns the row index. Optionally, you can retrieve a value from a different column in the same row. This node is similar to Excel's VLOOKUP function.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Input​

  • Table - DataTable to search in
  • Lookup Value - Value to search for in the specified column
  • Column Name - Column to search for the lookup value

Output​

  • Row Index - Zero-based index of the row containing the lookup value (-1 if not found)
  • Cell Value - Value from the target column in the found row (if Target Column Name is specified)

Options​

  • Target Column Name - Optional column name to retrieve a value from in the found row
    • If specified, returns the value from this column in the matching row
    • If not specified, only the row index is returned

Search Behavior​

  • The search uses a LIKE pattern with wildcards: column LIKE '%value%'
  • This means partial matches are found (e.g., searching for "john" finds "john@example.com")
  • Returns the first matching row if multiple matches exist
  • Returns -1 if no match is found
  • Search is case-sensitive

Example Usage​

Simple Lookup - Find Row Index​

// Table columns: ["ID", "Name", "Email", "Phone"]
// Row 0: ID="001", Name="John Doe", Email="john@example.com"
// Row 1: ID="002", Name="Jane Smith", Email="jane@example.com"
// Row 2: ID="003", Name="Bob Johnson", Email="bob@example.com"

var lookupValue = "002";
var columnName = "ID";

// Output:
// Row Index: 1

Lookup with Value Retrieval (VLOOKUP-style)​

// Same table as above
var lookupValue = "002";
var columnName = "ID";
var targetColumnName = "Email";  // In Options

// Output:
// Row Index: 1
// Cell Value: "jane@example.com"

Finding by Partial Match​

// Search for email domain
var lookupValue = "gmail.com";
var columnName = "Email";

// Finds: "john@gmail.com", "alice@gmail.com", etc.
// Returns the first match

Checking if Value Exists​

// Check if a user exists
var lookupValue = "john@example.com";
var columnName = "Email";

var rowIndex = /* Lookup DataTable output */;

if (rowIndex >= 0) {
    console.log("User found at row: " + rowIndex);
} else {
    console.log("User not found");
}
// Product lookup table
// Columns: ["ProductID", "ProductName", "Price", "Stock"]

var productId = "P001";
var columnName = "ProductID";
var targetColumnName = "Price";  // Get price for this product

// Output:
// Row Index: 5
// Cell Value: 29.99

Customer Information Retrieval​

// Customer database lookup
// Columns: ["CustomerID", "Name", "Email", "Phone", "Address"]

var customerId = "C12345";
var lookupColumn = "CustomerID";

// Get phone number
var targetColumn = "Phone";
// Output Cell Value: "+1-555-1234"

// In next lookup, get address
var targetColumn = "Address";
// Output Cell Value: "123 Main St, New York, NY"

Processing Based on Lookup Results​

// Search for a username
var username = "admin";
var columnName = "Username";
var targetColumnName = "Role";

var rowIndex = /* Row Index output */;
var role = /* Cell Value output */;

if (rowIndex >= 0) {
    if (role === "Administrator") {
        // Grant admin access
    } else {
        // Regular user access
    }
} else {
    // User not found - create new account?
}

Price Lookup for Order Processing​

// During order processing, look up product prices
var orderedProducts = ["SKU001", "SKU002", "SKU003"];
var totalPrice = 0;

for (var i = 0; i < orderedProducts.length; i++) {
    // Lookup each product
    var lookupValue = orderedProducts[i];
    var columnName = "SKU";
    var targetColumnName = "Price";

    var price = /* Cell Value output */;
    totalPrice += parseFloat(price);
}

Data Validation​

// Validate if an email already exists
var newEmail = "newuser@example.com";
var columnName = "Email";

var rowIndex = /* Row Index output */;

if (rowIndex >= 0) {
    // Email already exists - show error
    console.log("Email already registered");
} else {
    // Email is unique - proceed with registration
}

Tips​

  • The search is partial match by default (LIKE '%value%')
  • For exact matching, ensure your lookup value is unique enough
  • Returns -1 if no match is found - always check this before using row index
  • Only the first match is returned if multiple rows match
  • Use Target Column Name option for VLOOKUP-style operations
  • Column names are case-sensitive
  • Target Column Name can be the same as the search column
  • Efficient for one-time lookups; for multiple lookups, consider LINQ Query

Common Errors​

Empty Table Error​

Error: Table cannot be empty. Please provide a valid DataTable.

Solution: Ensure you're passing a valid DataTable object.

// Wrong - table is null
var table = null;

// Correct
var table = /* Valid DataTable */;

Empty Lookup Value Error​

Error: Lookup value cannot be empty. Please provide a value to search for.

Solution: Provide a non-empty lookup value.

// Wrong
var lookupValue = "";

// Correct
var lookupValue = "john@example.com";

Empty Column Name Error​

Error: Column name cannot be empty. Please provide a valid column name.

Solution: Provide a valid column name for searching.

// Wrong
var columnName = "";

// Correct
var columnName = "Email";

Target Column Not Found Error​

Error: Failed to retrieve value from target column 'InvalidColumn'. Please verify the column name exists in the table.

Solution: Verify the target column name exists and is spelled correctly.

// Table columns: ["ID", "Name", "Email"]

// Wrong
var targetColumnName = "Phone";  // Doesn't exist!

// Correct
var targetColumnName = "Email";

No Match Found​

When no match is found, Row Index returns -1. This is not an error, but you should handle it:

var rowIndex = /* Lookup result */;

if (rowIndex === -1) {
    // Handle not found case
    console.log("No matching record found");
} else {
    // Process found record
}

Accessing Cell Value When Not Found​

If no match is found but you try to access Cell Value, you may get an error or null:

var rowIndex = /* Lookup result */;
var cellValue = /* Cell Value output */;

// Safe approach
if (rowIndex >= 0) {
    // Only use cellValue if row was found
    console.log("Value: " + cellValue);
}

See Also​