I have the matrix below in Excel and want to import it into Access (2016) to then use in queries. The aim is to be able to lookup values based on the row and column. Eg lookup criteria of 10 and 117 should return 98.1.
Is this possible? I'm an Access novice and don't know where to start.
| . | 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 120 | 100.0 | 96.8 | 92.6 | 86.7 | 78.8 | 68.2 | 54.4 | 37.5 | 21.3 | 8.3 | 0.0 |
| 119 | 99.4 | 96.2 | 92.0 | 86.2 | 78.5 | 67.9 | 54.3 | 37.5 | 21.3 | 8.3 | 0.0 |
| 118 | 98.7 | 95.6 | 91.5 | 85.8 | 78.1 | 67.7 | 54.1 | 37.4 | 21.2 | 8.3 | 0.0 |
| 117 | 98.1 | 95.1 | 90.9 | 85.3 | 77.8 | 67.4 | 54.0 | 37.4 | 21.2 | 8.3 | 0.0 |
| 116 | 97.4 | 94.5 | 90.3 | 84.8 | 77.4 | 67.1 | 53.8 | 37.4 | 21.1 | 8.3 | 0.0 |
| 115 | 96.8 | 93.9 | 89.8 | 84.4 | 77.1 | 66.9 | 53.7 | 37.3 | 21.1 | 8.3 | 0.0 |
Consider creating a table with 3 columns to store this data:
Value1 - numeric
Value2 - numeric
LookupValue - currency
You can then use DLookup to get the value required:
?DLookup("LookupValue","LookupData","Value1=117 AND Value2=10")
If you have the values stored in variables, then you need to concatenate them in:
lngValue1=117
lngValue2=10
Debug.Print DLookup("LookupValue","LookupData","Value1=" & lngValue1 & " AND Value2=" & lngValue2)