Lookup value in table

[cjs321]

Hi,

I'm stuck on finding the correct syntax..

Issue: I need powerapps to lookup a value in a table (BRTLookupTable).

Columns in the Table (In order): From;To;A;B;C;D;E;F

The user should select a Belgium Road Tax zone from a dropdown, i.e BE-1, i've then put an IF statement together, that says, if BE-1 is selected, then, the BRT Zone is E etc.

Powerapps then needs to find a number value between the From and To columns.

so, let's say the weight of a delivery is 23000 kg, in my table, the last row, is From 18001 kg to 25000 kg, how do get powerapps to know that it should be looking on the last row, and column E to get the value it needs?

Kind Regards,

Con 🙂

[WarrenBelz]

Hi@cjs321 ,

You need a Lookup function. I am a little unsure of your search criteria - so I will cover two

I do not have your field/column names, but will use the following - YourList, DropDown1, BRTZone, WeightFrom, WeightTo, WeightText

From the TextBox with the weight - Item of a Form would be

Lookup(
   YourList,
   Value(WeightText.Text) >= WeightFrom &&
   Value(WeightText.Text) <= WeightTo
)

From DropDown1 with the zone (assuming the field choices are from the list)

Lookup(
   YourList
   BRTZone = DropDown1.Selected.BRTZone
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[cjs321]

Hi @WarrenBelz ,

I get this error? (Attached) 😞

[Max44]

Excel VLOOKUP Function

1. Summary. VLOOKUP is an Excel function to lookup and retrieves data from a specific column in the table. 
2. Lookup a value in a table by matching on the first column.
3. The matched value from a table.
4. =VLOOKUP (value, table, col_index, [range_lookup])
5. value - The value to look for in the first column of a table.

[WarrenBelz]

Hi @cjs321 ,

What is in ldmto.Text and what type of field is it in your data source.

If there is a valid number in there, Value(ldmto.Text) should produce a numeric value.

[cjs321]

Hi @WarrenBelz ,

In my ldmtokg Label, there is a calculated value- 13.6*1750 = 23800

Powerapps, then needs to look at my BRTLookupTable or BRTLookupCollection...Find which row 23800 is in between (it's in between the From & To values on the last row in the table), then it should also select the appropriate column (A,B,C,D,E or F) (Based off the BE-1,Be-2 etc) dropdown

[WarrenBelz]

OK @cjs321 ,

I was expecting a text box. If you already have a numeric value in a label then

Lookup(
   BRTLookupCollection,
   ldmokg >= BRTFrom &&
   ldmokg <= BRTTo
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[cjs321]

Hi @WarrenBelz,

Still not working 😞

I've attached an Excel sheet, with the BRTLookupTable on, so you can see what it's using 🙂

In this case, on the App, BE-1 is the selected Zone, and the calculated weight is 23800, so it should return a value of 24.41 

Hopefully it will make more sense with the attached table.

Thanks for your help 🙂

[WarrenBelz]

OK @cjs321 ,

I can see BRTLookupTable - I assume BRTLookupCollection is a straight exercise with 

ClearCollect(
    BRTLookupTable,
    BRTLookupCollection
)

I noticed that the cells in the Excel sheet were formatted as "General" rather than "Number" and am wondering if this might be part of the issue. Clearly, there is a incompatibility between the data in Power Apps and that in the data source. Try putting Value(BRTFrom) in a label and see if you get a valid response  - if so, 

Lookup(
   BRTLookupCollection,
   ldmokg >= Value(BRTFrom) &&
   ldmokg <= Value(BRTTo)
)

might work

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[cjs321]

Hi @WarrenBelz ,

i've formatted the cells as number now, but it still doesn't seem to work 😞

Is the syntax complete? In the syntax, i can't see where we tell powerapps to look in the appropriate column (A,B,C etc) 😞