Vlookup in Textbox - Step By Step

[robert_mika]

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:

'Category' and 'Name'

create Textobject:

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:

What's does the formula do:

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

Let say we have choose Jenny

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'

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

                                                                         =

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

                                                                     =

                                                                  London

which we can check in straight table:

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.makA8

for simplification:

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

HaimantaDawar

for pointing that this expression works only with unique values

For multiple values use:

sergey.makA8

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

[SergeyMak]

In this case $(=Category) gives you the value from this listbox and it will be used as a Field Name.

By default if you put any field name to the expression it will be translated as Only(FieldName)

But Only will return value of the field if it's only one distinct value, if not it will return Null(). That's why I use Concat with ', ' as separator to get all values from the selected field