LOOKUP with Multiple Conditions to Get A Value (Equivalent to DGET in Google Sheets)

[Tony_Insua]

Hi Gurus!
I have a read-only table in google sheets. In my app, the user will specify their Sex (Male or Female) and Classification (Class A or Class B) in their profile. These values can be pulled into a Virtual Column Summary Table easily enough.

I’d like to create a read-only Virtual Column Summary Table that tells the user their TARGET and CLASSES (for each ACTIVITY (1-to-3) based on the two conditions they specified in their profile.

So I’ll have a Virtual Columns that displays:
ACTIVITY 1 Target
ACTIVITY 2 Target
ACTIVITY 3 Target
ACTIVITY 1 Classes
ACTIVITY 2 Classes
ACTIVITY 3 Classes

There are three conditions: SEX, CLASSIFICATION, and ACTIVITY. SEX and CLASSIFICATION are dependant upon the user choice. ACTIVITY is NOT a selection as it’s a known variable so that’s something we can specify in the formulation.

In google sheets, finding:
ACTIVITY 1 Target value is a DGET function.

DGET(database, field, criteria)
DGET(A1:E12, TARGET, {“SEX”, “CLASSIFICATION”, “ACTIVITY”; (GENDER CHOSEN), (CLASSIFICATION CHOSEN BY USER), ACTIVITY 1})

What would be the equivalent expression to find TARGET for each ACTIVITY based on their chioces of SEX and CLASSIFICATION?

Finding the “CLASSES” value would be the similar expression I would imagine except change the field from TARGET to CLASSES.

Hope this makes senses.

[LeventK · Accepted solution]

@Tony_Insua
Assuming that the table in your post is your ACTIVITY table. You can create 3 virtual columns in USER PROFILE table, one for each activity and set the expression:

ANY(
    SELECT(
        Activity[TARGET],
        AND(
            [SEX] = [_THISROW].[SEX],
            [CLASSIFICATION] = [_THISROW].[CLASSIFICATION],
            "ACTIVITY 1" = [ACTIVITY]
        )
    )
)

ANY(
    SELECT(
        Activity[CLASSES],
        AND(
            [SEX] = [_THISROW].[SEX],
            [CLASSIFICATION] = [_THISROW].[CLASSIFICATION],
            "ACTIVITY 1" = [ACTIVITY]
        )
    )
)

[Tony_Insua]

@LeventK As always, your support is greatly appreciated. I’m nearing the completion of my first app thanks to you and others in this community. Super excited.

[LeventK]

You’re welcome