LookUp for Columns?

[bjnil]

Hi,

I'm most likely going about this the wrong way... but here goes:

Is there a way to select a column in a table based on a criteria and use this in a lookup formula?

I have a Table set ut as shown below, and I use a LookUp formula to select the appropriate record for my app.  The Columns named '100', '200',... etc represent the material properties of the material for a given temperature i.e. 100 F, 200 F, etc.

Referencing a field using LookUp is fairly easy when the column name is known. for example:

LookUp(Table; Material = MatrDropdown.Selected.Value; '100')

but....I want to grab the temperature from an TextInput box (TempBox) and when I try to parse it into the lookup formula it no longer returns the field from the table but the actual input from the textinput box.

Here's what I've tried:

LookUp(Table; Material = MatrDropdown.Selected.Value; Value(TempBox.Text))

I.e. if I put 100 as value in the textinputbox, then the value 100 is returned and not the referenced to the field in Table.'100' that corresponds to the selected material. I've tried adding "" or '' to the value, or simly renaming the columns to XXX, YYY, ZZZ etc but the same problem persists. Is there another/better way to reference a column in a table?

Material	100	200	300	400	500	600
CS- A106 B / API 5L B	20,00	20,00	20,00	20,00	18,90	17,30
CS- A333 Gr.6	20,00	20,00	20,00	20,00	18,90	17,30
CS- A671 C60/ A672 CC60	20,00	19,50	18,90	18,70	17,30	15,80
CS- API 5L X52	22,00	22,00	22,00	22,00	0,00	0,00
CS- A671 C70	23,30	23,10	22,50	21,70	20,50	18,70

Kind Regards

-Bjørn

[hpkeong]

Hi

After reading, I would suggest you re-tabulae it by tranposing them.

Material_Temp    CS-1   CS-2   CS03

100

200

300

Then You should be able to get what you want.

Just edit to insert a photo for referece.

Hope I am right.

TQ

[bjnil]

Hi,

I have not tried this yet.... but at first glance....won't the problem be the same; only transposed?

How can you extract the value for a given material from the resulting record? 😃

[BVora]

I am also facing this issue.

So what we need is an equivalent to Index formula which can search both Rows and Columns. Please let us know if there is any workaround for this problem.

[BVora]

I found the workaround. 

You need to put the data into the three columns as shown Below. Then in the lookup formula in the powerapps write this: LookUp(Table, Material = MatrDropdown.Selected.Value && Temp=TempBox.Text,Temperature).

Please let us know if there is a better way to achieve this.

Material	Temp	Temperature
CS- A106 B / API 5L B	100	20
CS- A333 Gr.6	100	20
CS- A671 C60/ A672 CC60	100	20
CS- API 5L X52	100	22
CS- A671 C70	100	23.3
CS- A106 B / API 5L B	200	20
CS- A333 Gr.6	200	20
CS- A671 C60/ A672 CC60	200	19.5
CS- API 5L X52	200	22
CS- A671 C70	200	23.1
CS- A106 B / API 5L B	300	20
CS- A333 Gr.6	300	20
CS- A671 C60/ A672 CC60	300	18.9
CS- API 5L X52	300	22
CS- A671 C70	300	22.5

[bjnil]

Hehe.... I managed to reach the same conclusion. Will try it out and report back.

[bjnil]

Yes, I can confirm that the solution works. It's a bit of a hazzle to rearrange the input data but it works.

In order to sort out the duplicate selections for the material dropdown I added a simple Filter(B313Data;Temperature = 100).

If I can figure out a better way I'll let you know. Thanks!

[hpkeong]

Hi @bjnil

In fact, what you presented in 3-Columns is the correct Tabluation for Database and the first one was Excel style.

I thought you wish to create a Matrix-Style table.

Anyway, glad that you had sorted it out.

Here is another workaround which is based on your MATRIX style data, though not prefered as normal databouse in making apps.

Matrix Styled Data solution

Hope this helps you if you wish this style.

Have a nice day.

G9 from HCMC.

[bjnil]

Thanks for the help!

I have not yet had a chance to try out the matrix solution but the first solution works fine! 😃