Forum Discussion
VLOOKUP with Unmatched Criteria
Hi,
I have 2 tables. Table 2 is replicated from Table 1 with the below sample data:
| Table 1 | Table 2 | |||
| COL 1 | COL 2 | COL 1 | COL 2 | COL 3 |
| 123 | A | 123 | A | X |
| 123 | B | 123 | B | Y |
| 456 | A | 456 | A | Z |
What I would like to achieve is to vlookup Table 2 with the below criteria:
T1.C1 = T2.C1 and T1.C2 <> T2.C2
End result is as per below in column 3.
| Table 1 | ||
| COL 1 | COL 2 | COL 3 |
| 123 | A | Y |
| 123 | B | X |
| 456 | A |
Is this possible in DAX?
Thank you.
Hi aarontanek
The following dax code should return what you need
Lookup =
VAR current_col1_T1 = Table1[COL 1]
VAR current_col2_T1 = Table1[COL 2]
VAR matching_rows_in_Table2 =
FILTER (
'Table 2',
'Table 2'[COL 1] = current_col1_T1 &&
'Table 2'[COL 2] <> current_col2_T1
)
RETURN
MAXX(matching_rows_in_Table2, 'Table 2'[COL 3])
2 Replies
- Cookistador
Super User
Hi aarontanek
The following dax code should return what you need
Lookup =
VAR current_col1_T1 = Table1[COL 1]
VAR current_col2_T1 = Table1[COL 2]
VAR matching_rows_in_Table2 =
FILTER (
'Table 2',
'Table 2'[COL 1] = current_col1_T1 &&
'Table 2'[COL 2] <> current_col2_T1
)
RETURN
MAXX(matching_rows_in_Table2, 'Table 2'[COL 3])It works! Thank you so much!
