Solved: Lookup using multiple criteria

Kyle_Richardson · 05-03-2019 04:01 AM

My current formula:

LOOKUP( [Date] , “Molding Manpower” , Production Date , Total People)

It returns the total people working in a workcell on a given date from my Molding Manpower table.

The problem is I have multiple shifts and operations. I need to match the “Date”, “Shift”, and “Mixer Number” and return the Total People.

I feel like in excel I would use filter() or multiple nested if() statements.

Kyle_Richardson

Solution:

ANY(
SELECT(
Molding Manpower[Total People],
AND(
([Production Date] = [_ThisRow].[Date]),
([Shift] = [_ThisRow].[Shift]),
([Mixer Number] = [_ThisRow].[Mixer Number])),
FALSE
)
)

[Hours on Job]

Thanks for pointing me in the right direction guys!

View solution in original post

Mike_A

@Kyle_Richardson - sounds like you just need some help with expression syntax. Appsheet supports both Filter and nested IF statements. You can find syntax help at Appsheet.com/support.

Kyle_Richardson

I certainly am having syntax issues in that I know how to use each of these properly on their own and I cannot figure out how to use them together to get what I need.

My formula now:

FILTER(
“Molding Manpower”[Total People],
AND(
([Production Date] = [_ThisRow].[Date]),
([Shift] = [_ThisRow].[Shift]),
([Mixer Number] = [_ThisRow].[Mixer Number])
)
)

This produces a list. I just need the single value of [Total People] in a row where it meets my 3 criteria.

Steve

As @Mike suggested, you should probably read the documentation. For instance, your FILTER() syntax is wrong: FILTER(“Molding Manpower”[Total People], ...) should not include [Total People] there. If you read about LOOKUP(), you’ll read that “LOOKUP() is effectively a wrapper to the SELECT() function.” If you look at SELECT(), you’ll see it’s what you want.

Kyle_Richardson

Solution:

ANY(
SELECT(
Molding Manpower[Total People],
AND(
([Production Date] = [_ThisRow].[Date]),
([Shift] = [_ThisRow].[Shift]),
([Mixer Number] = [_ThisRow].[Mixer Number])),
FALSE
)
)

[Hours on Job]

Thanks for pointing me in the right direction guys!

Steve

Perfect!

williamsanj13

thanks @Kyle_Richardson

System_My_Hao

I see the fomula ok but it seem very slow syncing.

Steve

A virtual column with an App formula expression that uses FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), or SELECT() is very likely to hurt sync time.