Continuous Lookup Table

[PrateekLowaleka]

Hello Everyone,

I have a table containing duplicate identifier values, however, the subsequent columns have unique values. I need to use the lookup to bring the correct value. Below is the example of the table:

Type             Start range    End range                       Correction %

xxx	0	0.999999	100
xxx	1	1.249999	75
xxx	1.25	1.499999	50
xxx	1.5	99999999	0

I have the type column value "xxx" coming in via a dropdown. Then I have an input field containing value 1.3. I'm trying to lookup this 1.3 value in this table between the start range and end range columns and based on the matching row the "Correction %" value of 50 should be populated.

Please help me achieve this. I handled this problem in excel using MATCH and INDEX functions. However, as ARRAYs are not supported in powerapps, I'm stuck here.

Thanks in Advance

[eka24]

I suggest you look at this document on LookUp function.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Your request is not clear. For instance, I can't see any 50 in the table displayed. Also how does the drop-down fit in?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[RandyHayes]

@PrateekLowaleka 

There are a variety of ways to solve this.  One that I would favor is the have the Items property of your dropdown be similar to this:

   GroupBy(yourTable, "Type", "_recs")

Now, to find the correct correction % in the list, your formula would be the following:

   LookUp(yourDropdown.Selected._recs, Value(yourTextInput.Text) >= 'Start range'  && Value(yourTextInput.Text) <= 'End range', 'Correction %')

Having the above in a label, for example, would display 50 if you have 1.3 in your text input control and your Dropdown set to xxx.

I hope this is helpful for you.

[PrateekLowaleka]

Hello eka24 and thank you for your reply. 50 is the correction value in the third row (Column name = Correction %) and 50 is desired because 1.3 falls between 1.25 and 1.499999 (same row).

Dropdown is needed as I'm using another table to look up a certain value based on the logged-in user's email address. For Example: after obtaining the user's email address using User().Email , I am using this email address as a lookup value in another table where I have value xxx (same as the above table). Alternatively, if there is no result from the lookup, error value will be shown. Below is the formula that I'm using:

IfError([
LookUp(
Table1,
'Employee email' = Email_ID_Dropdown.Selected.Value,
Qualifier
)
],["Not Applicable"])

Hope this clarifies 🙂

[PrateekLowaleka]

Hello RandyHayes and thank you for your reply. I understood the approach, however, I'm not able to use the Groupby function in the items property of the dropdown as I already have the following formula in the items property:

IfError([
LookUp(
Table1,
'Employee email' = Email_ID_Dropdown.Selected.Value,
Qualifier
)
],["Not Applicable"])

Please help me to merge both the formulas or create a new one. Really appreciate your detailed response with the solution. 

[RandyHayes]

@PrateekLowaleka 

I am not following why you would have a dropdown with one item in the dropdown?  A dropdown is about making a selection.

So perhaps if you can describe the relationship of that formula with the table of values you are trying to get a number from, that would be helpful.

[PrateekLowaleka]

Please allow me to explain my business case in detail.

I have created an application to help sales employees predict their incentives. Each employee is on a certain pay plan qualifier for payment and those details are confidential. My application is working perfectly when all the values are available for dropdown selection (pay plan qualifier). Now due to integrity issues, we're not supposed to expose all pay plans to all employees. Thus an excel table was imported containing the following

columns:

email id - pay plan

I'm using lookup on this table to get the pay plan based on the logged-in user. I tried doing it via a text label, but it did not work (used both Value and Text functions). I found a solution on this website where my desired output was getting populated via a dropdown. Thus the dropdown in my problem statement.

Regarding the formula, it is required as not all employees are eligible for that pay plan qualifier. Those who are eligible will have their payment adjusted based on the "Correction %" column. Rest of the employees will be paid at 100%. I hope this helps.

[RandyHayes]

@PrateekLowaleka 

You certainly need no dropdown for this then.

What you are doing is simply looking up the correction % from the list based on the Type as determined by the User.

I am not getting though, what is the correlation between Type in your original posted table and Qualifier from your single record dropdown.  Can you explain that further?

I am assuming Table1 is NOT the same table as the original post table.

[PrateekLowaleka]

Type and qualifier are the same, just different names. 

Table1:

Employee email - Job category - Weights - Qualifier

Table 2:

Type - Start range - End range - Correction %

Step 1: Generate email id using User().Email

Step 2: Lookup email id against Table1 and get value of "Qualifier" column

Step 3: Lookup "Qualifier" column value against Table2 "Type" Column, for matching records check the result between Start Range and End Range. Once the result is found in either of the 4 rows, get the "Correction %" value to use during payout calculation.

My earlier thought of using the dropdown was to give all pay plan values in the list for people who are not present in Table1 (these people are top management employees who want to evaluate incentives for a certain pay plan; for first line sales employees this feature will not be available and they should get their pay plan pre-populated.

[RandyHayes · Accepted solution]

@PrateekLowaleka 

So then the solution is much simpler.

Your Formula for your correction % should be:

Coalesce(
    LookUp(Table2,
       Type = LookUp(Table1, Email_ID_Dropdown.Selected.Value, Qualifier) &&
       Value(yourTextInput.Text) >= 'Start range'  && 
       Value(yourTextInput.Text) <= 'End range', 
       'Correction %'
    ),
    100
)

This will lookup the correction % from table2 based on the Type equaling the Lookup into table1 for the employee email and the returned Qualifier, and the value of your input being between the start and end range.

If none of the conditions are met in the Lookup criteria, Lookup will return a blank value...this is where Coalesce comes in to change it to 100 instead (based on your business logic you stated).