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)


    t1:

    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

    ]

    ;

    t2:

    load * inline [

    Category

    Helptext

    Name

    City,

    Salary,

    Sales

    ]

     

     

    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',
    fieldvalue(GetFieldSelections(Category)
    ,
    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')))

                                                                         =

                                                                      London

    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.


    Thanks:

    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),', '))