Skip to content
Browse all functions

Vlookup Spreadsheet Function for Arrays

spreadsheet/vlookup

vlookup (Vlookup Spreadsheet Function for Arrays) is a 1001fx API function in the group Spreadsheet: POST https://api.1001fx.com/spreadsheet/vlookup, 2 credits per call. It works as a ready-made module in Make and n8n or as a plain HTTP call.

2 credits per callHTTP APIn8nMake

Implements a vlookup function similar to the GSheet and Excel functions. Searches for a value in the first column of a table or an array of values, and then returns a value in the same column from a column you specify in the table or array.

Use cases

  • Look up a price or SKU in a product list without a spreadsheet
  • Map country names to codes or customer IDs to account managers
  • Enrich incoming orders with data from a reference table

Try it in the 1001fx app

Run vlookup in your browser with your API key – fill the form, click Run and get the answer or the file.

vlookup in the 1001fx app: the form with example values
Vlookup Spreadsheet Function for Arrays in the 1001fx app

Endpoint

POSThttps://api.1001fx.com/spreadsheet/vlookup
Header
X-API-KEY: YOURSECRETAPIKEY How to
Content type
application/json

Parameters

  • dataarray<object>required

    Array of lines with mixed values

  • lookUpValuestringrequired

    The value to be found in the first column of the data

    Example:
    Apple
  • columnnumberrequired

    The column to look up

    Min:
    0
    Example:
    2
  • ignoreCasebooleanoptional

    Sets case sensitivity for lookup operation

    Default:
    true
    Example:
    true

Examples

Look up, ignoring case

Finds the row with "Apple" and returns column 2 of it.

Input

Expects a json body
{
  "data": [
    {
      "Alice": 1,
      "Bob": 2,
      "Charly": 3,
      "Dave": 4
    },
    {
      "Alice": "Germany",
      "Bob": "USA",
      "Charly": "Austria",
      "Dave": "Switzerland"
    },
    {
      "Alice": 11,
      "Bob": 12,
      "Charly": 13,
      "Dave": 14
    },
    {
      "Alice": "Apple",
      "Bob": "Banana",
      "Charly": "Cherry",
      "Dave": "Dragonfruit"
    }
  ],
  "lookUpValue": "Apple",
  "column": 2,
  "ignoreCase": true
}

Output · HTTP 200

Response
{
  "result": "Banana"
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/vlookup' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"Alice":1,"Bob":2,"Charly":3,"Dave":4},{"Alice":"Germany","Bob":"USA","Charly":"Austria","Dave":"Switzerland"},{"Alice":11,"Bob":12,"Charly":13,"Dave":14},{"Alice":"Apple","Bob":"Banana","Charly":"Cherry","Dave":"Dragonfruit"}],"lookUpValue":"Apple","column":2,"ignoreCase":true}'

Look up

Finds the row with "Germany" and returns column 2 of it.

Input

Expects a json body
{
  "data": [
    {
      "Alice": 1,
      "Bob": 2,
      "Charly": 3,
      "Dave": 4
    },
    {
      "Alice": "Germany",
      "Bob": "USA",
      "Charly": "Austria",
      "Dave": "Switzerland"
    },
    {
      "Alice": 11,
      "Bob": 12,
      "Charly": 13,
      "Dave": 14
    },
    {
      "Alice": "Apple",
      "Bob": "Banana",
      "Charly": "Cherry",
      "Dave": "Dragonfruit"
    }
  ],
  "lookUpValue": "Germany",
  "column": 2
}

Output · HTTP 200

Response
{
  "result": "USA"
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/vlookup' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"Alice":1,"Bob":2,"Charly":3,"Dave":4},{"Alice":"Germany","Bob":"USA","Charly":"Austria","Dave":"Switzerland"},{"Alice":11,"Bob":12,"Charly":13,"Dave":14},{"Alice":"Apple","Bob":"Banana","Charly":"Cherry","Dave":"Dragonfruit"}],"lookUpValue":"Germany","column":2}'

Frequently asked questions

How is the data structured?
data is an array of rows, each row an object. The value is searched in the first column, and column selects the column of the result, counted from 1.
Is the lookup case sensitive?
Not by default: ignoreCase is true. Set it to false for an exact match.

Try vlookup now

Run it in your browser or create a free account and get 100 credits every month.

Changelog

  • 1.0.005.01.2023created
Ask 1001fx