Obtain value based on two other columns

[WiredCats]

This may be easy, but I am struggling with it. I want to lookup a value based on two other column values. Example:

Column 1 Coumn 2 Column 3
23 Red ABC
45 Blue XYZ

So, I want to use Column 1 = “23”, Column 2 = “Red”, and get a return value of “ABC”

Thanks
Matt

[LeventK]

@WiredCats

ANY(SELECT(TableName[Column3],AND([Column1]=23,[Column2]="Red")))

[Steve]

See also: AND(), ANY(), SELECT()

[WiredCats]

Thanks @LeventK and @Steve !

I also need to refer to columns in another table and check them against data in my current record. Here is what i have, but I get an error on the AND. I suspect it is my syntax for referring to the other table?:

ANY(SELECT(‘Other Table’[Column3],AND(‘Other Table’[Column1]=[_THISROW].[Column5],‘Other Table’[Column2]=[_THISROW].[Column6])))

Error: Condition AND(“Other Table”, “Other Table”) has an invalid structure: subexpressions must be Yes/No conditions

[WiredCats]

I did save myself some grief by removing the spaces in the secondary table name. This has revealed that i do not know how to refer to other columns in my current record/compariung a list value to a number.

Current state:

ANY(SELECT(OtherTable[Column3],AND(OtherTable[Column1]=[_THISROW].[Column5],OtherTable[Column2]=[_THISROW].[Column6])))

Error: Cannot compare List with Number in (OtherTable[Column1] = [Key].[Column1])

[Steve · Accepted solution]

Within a SELECT() row-match expression, you don’t need to specify the table name if referring to the table the SELECT() is being used on:

ANY(SELECT(OtherTable[Column3],AND([Column1]=[_THISROW].[Column5],[Column2]=[_THISROW].[Column6])))

[WiredCats]

Steve - Thanks! That did the trick!