Formula: FILE_LOOKUP

This article will explain provide details and examples of this formula syntax usage


Background

This formula syntax is used when you are trying to lookup data using a lookup file. Some reasons/examples you would use this are:

  • Coding state to region
  • Using a predetermined list of passcodes and making sure only those are allowed to take the survey. 

Formula Syntax

FILE_LOOKUP ( "FILE_NAME", LOOKUP_VALUE, "MATCH_COLUMN" , "FETCHED_COLUMN" )

  • "FILE_NAME": 
    • Is the name of the lookup file you have uploaded to your survey
    • Must be surrounded by quotes
  • LOOKUP_VALUE: 
    • This is the value you want to lookup in the uploaded file
  • "MATCH_COLUMN":
    • This the exact text in the header of the column you are trying to match with the lookup value
    • This must be surrounded by quotes
  • "FETCHED_COLUMN":
    • The exact text in the corresponding header column you are trying to return
    • This must be surrounded by quotes 

Examples

Example 1
You are asking each respondent what state he/she lives in and you want to code a hidden variable to a region based on a lookup file. 

You will need to program the following:

  • A drop-down question asking which state the respondent lives in.
    • This does not have to be a drop-down but this is most common. 
    • For this example, we will call this 'Q1'
  • Program your hidden variable and add the response list for your regions
    • For this example, we will call this 'REGION'
  • Prepare and upload your lookup file
    • Lookup files must be in .csv format
    • For this example, we will call the lookup file 'REGION_LOOKUP'
    • Your lookup file will look like this:
  • Actual formula used:
    • Set Variable "REGION" to 
      FILE_LOOKUP ( "REGION_LOOKUP", Q1, "STATE" , "REGION" )

Example 2

You have a predetermined list of passcodes and you only want those to be allowed to take the survey. If its not on the list, you want to terminate them.

  • Prepare and upload your lookup file
    • Lookup files must be in .csv format
    • For this example, we will call the lookup file 'PASSCODES'
    • Your lookup file will look like this: 
  • Actual formula used:
    • Only if: 
      FILE_LOOKUP ( "PASSCODES", RESP_TOKEN, "PWD" , "PWD") = ""
      Action: Terminate
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us