1. Checkbox
  2. Guides
  3. Use Cases

Index Match/VLOOKUP

SKIP AHEAD TO 
Introduction
Step-by-step instructions

 

This design pattern requires knowledge of the following pages:

 

Introduction

Index Match/VLOOKUP functionality allows you to look up values in a table based off of other rows and columns.

Take, for example, a fruit market.

11.PNG

In this fruit market, each fruit item is allocated a quantity, price and an owner.

Using Index Matching could allow us to determine which fruits each owner owned. It could also, for instance, allow us to find which fruits had more than 100 fruits in quantity, or even which fruits have are priced at 4 dollars.

 

Step-by-step instructions

For this example, let's assume we would like to determine which fruits each owner owns.

To create the required Index Match:

  1. Create a Table (this will capture user data).

    1.PNG

  2. Configure the Table for collection of user data.

    2.PNG

  3. Connect your Table block to a Merge block.

    3.PNG
  4. Within the Property Editor of your Merge, create a new List for each for each Table Cell. Then add the field code for each cell into the relevant Variable REF slot. Your Table Cells may have different field codes to the examples shown below.

    4.PNG

  5. Connect your Merge block to a Filter block.

    5.PNG

  6. In this example, every time the owner of the fruit is "Bob", we want to recall the "Item" Bob owns. To do this:
  • In the General section, set LIST8 == "Bob" as Filter Condition. This will search through LIST8 and find all occurrences of "Bob".
  • In the Filter Outputs section, add the corresponding List that you want to return into the Filter LIST slot. In this case LIST5 corresponds to the list of fruit items.
    • You could put in any of the other Lists into different Filter LIST slots too, like LIST6 to return the individual quantities of the fruit Bob owns, or LIST7 for the individual prices of the fruit Bob owns.
  • Finally, note that LIST21 is the output list of all the fruit items who have the owner Bob.

    6.PNG

Now you have index-matched fruits owned by Bob.

To refer to this list in other areas of the Studio, use braces as follows:

Using_Variables.png

And your output should show the following:

Capture.PNG