Vlookup in Textbox - Step By Step

    Playing recently with many functions I have found out a way to get a value from a table  based on two fields

    This solution will work like Excel INDEX/MATCH/MATCH with hard coded vertical dimension and dynamic horizontal dimension.


    Very useful when you have  large data set but want to return only one value per category


    Data Model

    (Copy and Pasted below code into Edit Script window and reload)


    Load * inline [


    Helptext, Name, City, Salary ,Sales

    Block, Bob, Warsaw, 100, 25

    Color, John Doe, Budapest, 200, 358

    Topics, Jenny, London, 300, 400

    Font, Curt, Cair, 400, 800

    Data ,Martin, Madrit, 500, 1500

    Group ,Mirek, Mins, 600, 200

    Total ,Stan ,Utah, 700, 158




    load * inline [










    First table is our Data Table the second works as Headers of the dimensions.




    Create two ListBoxes:

    23-Apr-15 4-28-49 PM.jpg


    'Category' and 'Name'


    23-Apr-15 3-59-45 PM.jpg

    create Textobject:

    23-Apr-15 4-01-18 PM.jpg

    and add this expression into Text window:


    =if( (GetSelectedCount(Category)+ GetSelectedCount(Name))<2
    ,'Please choose  value from two fields',
    FieldIndex( 'Name', GetFieldSelections(Name))))


    Press 'OK'


    Your result should be similar to this:

    23-Apr-15 4-06-46 PM.jpg


    What's does the formula do:


    GetFieldSelections(Name) - is returning value (Name) from the Name field    'Jenny'

    Let say we have choose Jenny

    23-Apr-15 4-11-05 PM.jpg

    FieldIndex( 'Name', GetFieldSelections(Name))                     will return position of Jenny in the Name field


    FieldIndex( 'Name', GetFieldSelections(Jenny)) = 3

    GetFieldSelections(Category) this will return value from Category Field ='City'

    23-Apr-15 4-17-06 PM.jpg

    fieldvalue(GetFieldSelections(Category) ,FieldIndex( 'Name', GetFieldSelections(Name)))


    fieldvalue(GetFieldSelections('City') ,FieldIndex( 'Name', GetFieldSelections('Jenny')))



    23-Apr-15 4-20-57 PM.jpg

    which we can check in straight table:

    23-Apr-15 4-24-30 PM.jpg

    The rest of the code is a wrapper which will ensure that only when value from two fields are choosen the result will be displayed.


    Sergey Makushinsky

    for simplification:

    =IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',$(=Category))

    Haimanta Dawa

    for pointing that this expression works only with unique values

    For multiple values use:

    Sergey Makushinsky

    =IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',Concat(DISTINCT $(=Category),', '))