Forum Discussion
Need Help in Lookup value with Conditions
Hi All, I am trying to lookup value with 2 tables and with a condition. May I seek some advices here without typing the whole list in custom column(As the Original list is much larger)
Is there any way I could lookup as below,
if "Table 2"(Size 1) = "Table 1"(Width) ± 10
AND if "Table 2"(Size 2) = "Table 1"(Height) ± 10
Then "Table 1"(Size)
Table1: (Lookup source)
| Size | Width | Height | (w x h) |
| DL | 110 | 220 | 110 x 220 |
| C4 | 229 | 324 | 229 x 324 |
| C5 | 162 | 229 | 162 x 229 |
| C6 | 114 | 162 | 114 x 162 |
Table 2 (Lookup destination):
| Size1 | Size2 | CombineSize | Desired Result | |
| 117 | 230 | 117 x 230 | DL | |
| 110 | 227 | 110 x 227 | DL | |
| 117 | 230 | 117 x 230 | DL | |
| 113 | 168 | 113 x 168 | C6 | |
| 110 | 220 | 110 x 220 | DL | |
| 110 | 220 | 110 x 220 | DL | |
| 229 | 324 | 229 x 324 | C4 | |
| 110 | 220 | 110 x 220 | DL | |
| 162 | 230 | 162 x 230 | C5 |
Appreciated if there is any solution for this!
ngct1112 , Create a new column like this in table 2
maxx( filter(Table1,
'Table 2'[Size 1] >= 'Table 1'[Width] -10 && 'Table 2'[Size 1] <= 'Table 1'[Width] +10
&& 'Table 2'[Size 2] >= 'Table 1'[Height] -10 && 'Table 2'[Size 2] <= 'Table 1'[Height] +10 ) , 'Table1'[Size])
2 Replies
ngct1112 , Create a new column like this in table 2
maxx( filter(Table1,
'Table 2'[Size 1] >= 'Table 1'[Width] -10 && 'Table 2'[Size 1] <= 'Table 1'[Width] +10
&& 'Table 2'[Size 2] >= 'Table 1'[Height] -10 && 'Table 2'[Size 2] <= 'Table 1'[Height] +10 ) , 'Table1'[Size])amitchandak, thanks for your help. It really helps
And side questions, why I can't put your formula on "Add Measure" since I find Parameter is not application to column?
and with this formula, is it possible I could add else "Table 2"(Combine Size")?
