Advanced VLOOKUP: JSON Datasets

// Data → Get Data → From JSON → Your API URL
// Power Query Editor:
// 1. Parse JSON
// 2. Expand Column → Record
// 3. Expand Column → List → To Table
// 4. Expand nested objects
// 5. Close & Load

📄 Result: Clean table A1:D100

[
  {"id":1, "name":"John", "sales":45000, "dept":"Sales"},
  {"id":2, "name":"Jane", "sales":52000, "dept":"Marketing"},
  {"id":3, "name":"Bob", "sales":38000, "dept":"Sales"}
]

A    B      C       D
id   name   sales   dept
1    John   45000   Sales
2    Jane   52000   Marketing
3    Bob    38000   Sales

=VLOOKUP(1, A:D, 3, FALSE)     → 45000 (John's sales)
=VLOOKUP("Jane", B:D, 2, FALSE) → 52000

✅ Works after Power Query conversion

=INDEX(C:C, MATCH(1, A:A, 0))                    → ID→Sales
=INDEX(C:C, MATCH("Sales"&"John", D:D&B:B, 0))  → Dept+Name

=INDEX(sales_col, MATCH(emp_id, id_col, 0))

=VLOOKUP(G2, A:E, MATCH(H1, 1:1, 0), FALSE)

G2 = Employee ID
H1 = "Sales" or "Bonus" (dynamic)
1:1 = Header row

✅ Column index finds header automatically

[
  {
    "id":1,
    "name":"John",
    "metrics": {
      "sales":45000,
      "bonus":5000
    }
  }
]

// Power Query:
// 1. Parse JSON
// 2. Expand "metrics" column
// 3. → sales | bonus columns

Result:
A  B    C      D
1  John  45000  5000

// Alt+F11 → Insert Module
Public Function VLOOKUPWEB(apiUrl, field, timeout, token, body)
    ' Fetches JSON + extracts field
    ' =VLOOKUPWEB("https://api.com/data", "sales", 5)
End Function

=VLOOKUPWEB("https://api.example.com/employees", "salary", 3)

=VLOOKUP(id, json_table, {2;3;4}, FALSE)

📦 Spills: name, sales, dept in 3 cells

Alt: =FILTER(B:D, A:A=id)

=IFERROR(
    VLOOKUP(id, json_table, col_num, FALSE),
    "Employee not found"
)

=IFNA(INDEX/MATCH..., "No data")