Lookup Function using Excel as a source & multiple criteria. In Excel this would be Match / Vlookup or Xmatch

[Shell1]

Hi all,

I have an app of employees and the hours they work each month. Each employee might have different hours depending on the month. The table below shows my Excel table of data. The numbers are the hours the employee works in that month. 

Data table

In the app, I want these hours to come up based on when the user logs into the app. 

At the moment, I have a simple 2 column table where this is working for the employees whose hours are the same. This is a simple table where column A is the month and column B is the hours. This is below. and works. 

However, I cannot for the life of me get the table with all employees and months to work. I need a) the list to filter to the user. I can do this using Filter = TableName, Employee,User ().FullName. I cannot then however get the search or lookup to work and bring any values through. 

SEARCH does not work - it says the text value is incorrect and expects a 0 value. 

Help would be appreciated.

Note - I HAVE to use Excel, no SharePoint access (policy).

[RandyHayes]

@Shell1 

It seems you just need to add the User into your formula.

Please consider changing your Formula to the following example:

LookUp(monthly_hours, Month = 'MONTH SHOW'.Text && Employee = "Frank Norris", 'Hours in month')

I hope this is helpful for you.

[Shell1]

Hi RandyHayes, Thanks for your reply.

I've brought the table (in the original message) up to a collection which is filtered per user on OnVisible. PowerApps has brought it up like the below as each user is assigned one row. This collection is called "allhours". 

I tried your formula but because each month is its own column, it isn't working. 

The code in the original table (and photo)was just for a two column table which worked nicely. The Lookup(Table, Month = Monthly hours etc) is based on this table below. 

However, because I want to add more complexity and each column is now it's own month, there isn't a 'result' column like there was previously when it was just the two column table. 

When I tried that forumula, I had to select a column - but the column month will change every month.

[RandyHayes]

@Shell1 

Ah gotcha.

You can skip the collection unless there is a need to duplicate all of the data in your app memory.

Yes, you cannot dynamically use a column by reference.

So you will have to do it manually.

With(LookUp(monthly_hours, Employee = "Frank Norris", 
    Switch('MONTH SHOW'.Text,
        "January", January,
        "February", February,
        "March", March,
        ...etc.
    )
)

As you can see, the original two column table you had is easier to work with from the PowerApp perspective.  BUT, since there are only 12 months in your table and they are pretty much never going to change, the manual entry is somewhat tolerable.

[Shell1 · Accepted solution]

Thanks!

I'm getting the following error:

The 'month show' text box has a variable context in it. This worked okay on the original 2 column table, but possibly could be causing an issue now?

[RandyHayes · Accepted solution]

@Shell1 

Oops, sorry, I missed a closing paren in the example.  Should be:

With(LookUp(monthly_hours, Employee = "Frank Norris"), 
    Switch('MONTH SHOW'.Text,
        "January", January,
        "February", February,
        "March", March,
        ...etc.
    )
)

[Shell1]

Thats perfect. Thanks so much! I'll spend some time understanding this particular function better.

Is there a way to also include an IF where IF MONTH SHOW = blank, it returns "all" (which is all hours in my table).

Can't figure out where this could go. 

[RandyHayes]

@Shell1 

Would All be a sum of the months or do you already have this in the excel table?

You could add an "All" to your dropdown then your formula would be (if you have a column with the total):

With(LookUp(monthly_hours, Employee = "Frank Norris"), 
    Switch('MONTH SHOW'.Text,
        "All", yourAllColumnName,
        "January", January,
        "February", February,
        "March", March,
        ...etc.
    )
)

Or, if you have no column:

With(LookUp(monthly_hours, Employee = "Frank Norris"), 
    Switch('MONTH SHOW'.Text,
        "All", January + February + March + ...etc.. + December,
        "January", January,
        "February", February,
        "March", March,
        ...etc.
    )
)

And, your Dropdown Items property would be:

Ungroup(
    Table({Items:Table({Value:"All"})},
          {Items: Calendar.MonthsLong()}
    ), 
    "Items"
)