article Vlookup in Textbox - Step By Step in Member Articles

Vlookup in Textbox - Step By Step

robert_mika — Thu, 23 Apr 2015 12:16:33 GMT

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 [

Re: Vlookup in Textbox - Step By Step

SergeyMak — Fri, 24 Apr 2015 00:34:41 GMT

Very interesting, but

I think it's easier just use the following expression

=IF(isNull(City),'Please select Category and Name',City)

If you don't need message 'Please select Category and Name'

= City

would be enough

Regards,

Sergey

Re: Vlookup in Textbox - Step By Step

robert_mika — Fri, 24 Apr 2015 04:16:33 GMT

The idea here is to have dynamic Category field.

Your solution works only when the Category is 'City'

Re: Vlookup in Textbox - Step By Step

SergeyMak — Fri, 24 Apr 2015 04:20:43 GMT

What do you think about the following expression for the textbox?

=IF(isNull($(=Category)),'Please select Category and Name',$(=Category))

Re: Vlookup in Textbox - Step By Step

robert_mika — Fri, 24 Apr 2015 04:30:54 GMT

Does it work for you?

Re: Vlookup in Textbox - Step By Step

SergeyMak — Fri, 24 Apr 2015 04:33:54 GMT

Almost...

It works if you modify it a bit, but still a bit simplier

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

Re: Vlookup in Textbox - Step By Step

robert_mika — Fri, 24 Apr 2015 04:50:35 GMT

Good one.

It is simpler.

Re: Vlookup in Textbox - Step By Step

Fri, 24 Apr 2015 06:29:17 GMT

It is not working if my inputs are repeated. Eg. for city if I have same city name for two or more persons then only for the 1st person its showing

Re: Vlookup in Textbox - Step By Step

robert_mika — Fri, 24 Apr 2015 07:10:16 GMT

Hi Dawar,

Thank you for your input.

This has not been designed to work on multiple values - for that we would probably need a custom function in VBS or JS.

I will add this comment to the docs.

Re: Vlookup in Textbox - Step By Step

SergeyMak — Fri, 24 Apr 2015 07:18:26 GMT

Hi Haimanta,

Try this expression for a textbox

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

It's getting more complicated

Re: Vlookup in Textbox - Step By Step

Fri, 24 Apr 2015 07:35:37 GMT

Sergey,

Its working smooth! But do you mind elaborating this part "Concat(DISTINCT $(=Category)" a bit

Re: Vlookup in Textbox - Step By Step

SergeyMak — Fri, 24 Apr 2015 13:31:24 GMT

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