Forum Discussion
Pulling data from a range in a different table
Hi all,
Hopefully someone might have the answer to this query.
I have two tables which I need to compare Table 1 to Table 2 with the following criteria:
Table1[from_code] = Table2[code]
and
Table1[from] >= Table2[m] and Table1[from_c] >= Table2[c]
and
Table1[to] <= Table2[m] and Table1[from_c] <= Table2[c]
Table 1
| reference | from_m | from_c | to_m | to_c | from_code | to_code |
| Ref 2 | 120 | 5 | 120 | 45 | AAA | AAA |
Table 2
| code | m | c | long | lat |
| AAA | 100 | 0 | -0.69854 | 52.93361 |
| AAA | 100 | 15 | -2.16343 | 52.15026 |
| AAA | 110 | 5 | -0.69527 | 52.93401 |
| AAA | 120 | 0 | -0.25471 | 51.53321 |
| AAA | 120 | 15 | -2.8848 | 53.3538 |
| AAA | 120 | 32 | -2.16343 | 52.15026 |
| AAA | 120 | 43 | -2.74384 | 52.70946 |
| AAA | 120 | 55 | -2.96232 | 53.47024 |
| AAA | 120 | 60 | 0.020622 | 51.48446 |
The result I want is a 3rd table with the below:
| reference [from Table1] | long [from Table2] | lat [from Table2] |
| Ref 2 | -2.8848 | 53.3538 |
| Ref 2 | -2.16343 | 52.15026 |
| Ref 2 | -2.74384 | 52.70946 |
So using the Table 1 reference line and pulling back the long and lat from Table 2 split out into multiple lines.
Any ideas would be massively helpful!
Hi TWolsten ,
This part of the judgment condition seems to be somewhat incorrect. But the desired result can be obtained by creating the following formula.
M = IF(120>=MAX(Table2[m]) && 5>=MAX(Table2[c]) ,1,0)
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi TWolsten ,
This part of the judgment condition seems to be somewhat incorrect. But the desired result can be obtained by creating the following formula.
M = IF(120>=MAX(Table2[m]) && 5>=MAX(Table2[c]) ,1,0)
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.