Lookups and Cross-Reference
Excel VLOOKUP and XLOOKUP match rows from one table to another using a key column. VisiData achieves this with the join workflow: mark a key column with ! on both sheets, then join with &. The result is equivalent to VLOOKUP but works on any column, in any direction.
Learning Focus
The join workflow is: (1) mark key column ! on Sheet 1 → (2) open Sheet 2 → (3) mark same key column ! → (4) select both sheets in Sheet List → (5) press & → choose join type. This replaces VLOOKUP entirely.
Lookup Function Reference
| Excel / Google Sheets | VisiData Equivalent | Description |
|---|---|---|
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE) | Set key ! on both → & → inner | Exact match lookup |
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B) | Same join workflow | Modern lookup |
=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0)) | Same join workflow | Index + Match |
=CHOOSE(n, A2, B2, C2) | = [col_a, col_b, col_c][n-1] | Choose from list |
=HYPERLINK(url, text) | Navigate to URL cell | Click URL in cell |
=TRANSPOSE(A1:C3) | T (transpose sheet) | Swap rows and columns |
=UNIQUE(A:A) | Shift+F on column | Distinct values |
=SORT(UNIQUE(A:A)) | Shift+F then [ or ] | Sorted distinct values |
=SORTBY(A:A, B:B, 1) | ! on sort column → g[ | Multi-column sort |
=SEQUENCE(10) | i (incremental column) | Generate 1–10 sequence |
=ROW() | = rownum + 1 | Row number (1-indexed) |
VLOOKUP → Join Workflow
The VisiData join workflow is more powerful than VLOOKUP because it:
- Works bidirectionally (not just left-to-right)
- Returns multiple matching columns at once
- Supports 7 join types (inner, outer, full, diff, append, extend, merge)
Step-by-Step Join
# Open the main sheet (e.g., servers)
vd ~/github/practice-folder/visidata/03-join/01-servers.csv
# Mark the join key
# Move to 'hostname' column → press !
# Open second sheet (roles lookup table)
# Open via command or Sheet List
# Press gS to view Sheet List, or open new file:
# (in another terminal: vd ~/github/practice-folder/visidata/03-join/02-roles.csv)
# Both sheets must be open at the same time
# Use Shift+S (Sheet List) to see all open sheets
# Select both sheets in Sheet List
# Navigate to Sheet List → Shift+S
# Press s on each sheet to select both
# Join the sheets
&
# Choose join type: inner
Join Types
| Type | Behavior | Excel Equivalent |
|---|---|---|
inner | Only rows matching keys in ALL sheets | VLOOKUP (exact match only) |
outer | All rows from first sheet + matches from second | LEFT JOIN |
full | All rows from all sheets | FULL OUTER JOIN |
diff | Rows NOT matching any other sheet | ANTI-JOIN (highlight gaps) |
append | Stack all rows (no key matching) | Stack / VSTACK |
extend | First sheet with extra columns from others | XLOOKUP (add columns) |
merge | First sheet; fill empty cells from second | Merge / Combine |
Complete Join Example
# Step 1: Open servers sheet
vd ~/github/practice-folder/visidata/03-join/01-servers.csv
# Mark key: move to 'hostname' → !
# Step 2: Open roles sheet
# Use Shift+S to open Sheet List
# Then navigate to open another file:
# Press o → Enter path: ~/github/practice-folder/visidata/03-join/02-roles.csv
# Mark same key: move to 'hostname' → !
# Step 3: Join
# Open Sheet List: Shift+S
# Select both sheets: s on each
# Press & → choose: extend
# → Servers sheet now has role columns from roles sheet
INDEX/MATCH — More Flexible VLOOKUP
When you need to look up by a non-leftmost column:
# Excel: =INDEX(B:B, MATCH(A2, C:C, 0))
# — find value in B where C matches A
# VisiData: same join workflow
# The join key (!) can be ANY column
# VisiData matches on the key regardless of column position
# Example: join on 'role_id' (not the first column)
# Move to 'role_id' in servers sheet → !
# Move to 'role_id' in roles sheet → !
# & → inner
CHOOSE — List-Based Column Selection
# Excel: =CHOOSE(2, name, salary, department)
# → returns salary (index 2)
# VisiData:
=
# Enter: [name, salary, department][1] # Python 0-indexed → index 1 = salary
TRANSPOSE
vd ~/github/practice-folder/visidata/07-melt/01-quarterly.csv
# Wide format: Q1, Q2, Q3, Q4 as columns
T
# Opens Transposed Sheet: quarters become rows
UNIQUE + SORT
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
# Get unique departments
# Move to 'department' column
Shift+F
# → Frequency Sheet: one row per unique department value
# Sort alphabetically
[
# → departments sorted A→Z
SEQUENCE — Generate Row Numbers
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Excel: =SEQUENCE(10) → generates 1,2,3,...,10
# VisiData: add incremental column
i
# Name: seq
# → 1, 2, 3, ... for every row
Multi-Sheet Join (Three Tables)
# Join servers + roles + deployments
vd ~/github/practice-folder/visidata/03-join/01-servers.csv
# Mark 'hostname' as key: !
# Open roles
# Open Sheet List → Shift+S → o → ~/github/practice-folder/visidata/03-join/02-roles.csv
# Mark 'hostname' as key: !
# Open deployments
# Shift+S → o → ~/github/practice-folder/visidata/03-join/03-deployments.csv
# Mark 'hostname' as key: !
# Select all 3 sheets in Sheet List: s on each
# & → extend
# All three tables combined on 'hostname'
Hands-On Practice
# VLOOKUP exercise: enrich servers with role info
vd ~/github/practice-folder/visidata/03-join/01-servers.csv
# 1. Mark 'hostname' as key: !
# 2. Open Sheet List: Shift+S
# 3. Navigate to open roles sheet
# 4. Mark 'hostname' as key on roles sheet: !
# 5. In Sheet List, select both sheets: s on each
# 6. Press & → choose: extend
# 7. Verify servers now has role columns
# 8. Press Ctrl+S → save as /tmp/servers_enriched.csv
# UNIQUE exercise
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
# Move to 'department' → Shift+F
# Press [ to sort alphabetically
# Note: each unique department = one row