LtCmdrData
Board Regular
- Joined
- Jan 24, 2018
- Messages
- 58
- Office Version
- 365
- Platform
- Windows
Hello Everyone,
I need help with a lookup formula that will allow me to enter two inputs and then lookup a value in a table that has multiple ranges (see below). The two inputs are weight and miles. Each of the inputs has multiple ranges to find the correct corresponding value. In essence it should look down and across in the table, in this case to find 0.15 and then multiple that times 2157 to equal 323.55. I am running Windows 10 with Office 365 but I don't have access to new formulas like XLOOKUP and FILTER yet so I need something older. I'm thinking maybe some type of INDEX/MATCH or SUMPRODUCT might work but I just don't know how to apply it given these number ranges. Any and all help would be much appreciated. Thank you.
I need help with a lookup formula that will allow me to enter two inputs and then lookup a value in a table that has multiple ranges (see below). The two inputs are weight and miles. Each of the inputs has multiple ranges to find the correct corresponding value. In essence it should look down and across in the table, in this case to find 0.15 and then multiple that times 2157 to equal 323.55. I am running Windows 10 with Office 365 but I don't have access to new formulas like XLOOKUP and FILTER yet so I need something older. I'm thinking maybe some type of INDEX/MATCH or SUMPRODUCT might work but I just don't know how to apply it given these number ranges. Any and all help would be much appreciated. Thank you.
Miles | |||||||||
Weight | Miles | Rate | Weight | 1-499 | 500-999 | 1000-1499 | 1500-1999 | 2000-9999 | |
2157 | 1350 | 325.55 | 1-499 | 0.21 | 0.23 | 0.24 | 0.33 | 0.34 | |
500-999 | 0.15 | 0.19 | 0.22 | 0.24 | 0.28 | ||||
1000-1999 | 0.12 | 0.15 | 0.19 | 0.21 | 0.23 | ||||
2000-9999 | 0.08 | 0.13 | 0.15 | 0.16 | 0.22 | ||||
10000-19999 | 0.07 | 0.07 | 0.08 | 0.11 | 0.13 |