Lookup function or vlookup?

[NPatel12498]

Hi,

I am trying to pull the per diem value from excel based on what is selected in the Required Meals drop down and the zip code.

Excel:

Powerapps Gallery, this is missing the zip code field, but I will be adding it:

[cdwhite · Accepted solution]

Hi @NPatel12498 ,

I've tried to replicate your scenario on my end. Hopefully I am close! Here's what I have done.

Firstly, if the spreadsheet data doesn't change much, I would store it as a collection in the app. This will save you and users having to constantly do lookups back to the spreadsheet. So that would be something like:

ClearCollect(
    colLocationExpenseBudget,
    ExcelSpreadsheetTable)

I then have a dropdown box to select the required meal. The dropdown box has Breakfast, Refreshments, Lunch & Dinner as selectable options.

We can use a combination of the With function and Switch function to get the relevant value needed. In the With function, we perform the lookup to the collection once, based on the Zip Code required. I've hardcoded the value but this can also be dynamic. Using the record stored from the With function, we can then perform the Switch based on the dropdown value for selected meal:

With(
    {
    // Perform the lookup once to get the relevant row in collection
        tvMeals: LookUp(
            colLocationExpenseBudget,
            ZipCode = 99775
        )
    },
    // Use Switch to determine which value to retrieve
    Switch(
        dpdRequiredMeal.Selected.Value,
        "Breakfast", tvMeals.Breakfast,
        "Refreshments", tvMeals.Refreshments,
        "Lunch", tvMeals.Lunch,
        "Dinner", tvMeals.Dinner
    )
)

I've tested at my end by changing the selected meal and the correct $ value is returned each time. Hopefully the above method will help you!
------------------------------------------------------------------------------------------------------------------------------
If I've answered your question, please mark your post as Solved. You can accept more than one post as a solution.

If my response was a good one, please give it a Thumbs Up!

Visit my blog: https://platformsofpower.net

[NPatel12498]

As silly of a question this may be,

where do I enter these formulas in? I have a button on my screen that patches the data:

[NPatel12498]

@cdwhite hope you had a great thanksgiving!

Could you please guide me as to where I should be putting the code you provided?

[cdwhite]

Hi @NPatel12498 ,

From your original post, it looks like you want to get a value based on the selected meal. What did you want to do with the value? Is it just for display purposes or do you need to update an entry in a data source?

You already have the Required Meal dropdown, so the code I provided above would work if adding into a the Text property of label control to show the £££ (update the dropdown control reference to yours).

[NPatel12498]

Yes,

Based on the meal that is selected and the zip code for that entry, it should pull the per diem rate from the excel file. In the gallery, it will be displayed but in the background there is another column that takes that per diem rate and does a calculation. 

[NPatel12498]

@cdwhite 

Thank you so much for your help! I had to split the code up a bit. I put the clear collect formula into my button and kept the with and switch formula within the value property of the control within my editable gallery. 

[cdwhite]

Great to hear, thanks @NPatel12498 

FYI, if you wanted to retrieve the value per line in your gallery, you can also achieve it by tweaking the code slightly. You could add a label to the gallery and set the Text property to something like below - where you reference the Zip code of the line to perform the retrieval.

With(
    {
    // Perform the lookup once to get the relevant row in collection
        tvMeals: LookUp(
            colLocationExpenseBudget,
            ZipCode = ThisItem.ZipCode
        )
    },
    // Use Switch to determine which value to retrieve
    Switch(
        dpdRequiredMeal.Selected.Value,
        "Breakfast", tvMeals.Breakfast,
        "Refreshments", tvMeals.Refreshments,
        "Lunch", tvMeals.Lunch,
        "Dinner", tvMeals.Dinner
    )
)

 Glad you got what you needed though, happy coding!