mrxlsx
Board Regular
- Joined
- Dec 15, 2012
- Messages
- 95
Hello,
Good morning everyone here,
We have two sheets and first sheet with the following information where I need to run a VLOOKUP or INDEX MATCH to fetch the FreightKey from second sheet.
In Sheet-1
<tbody>
</tbody>10 Air AI20
4 Sea SE20
8 Rail RA20
200 Road RO200
58 Sea SE60
In Sheet-2
<tbody>
</tbody>
I have some items to be shipped. The freight will be based on the weights of the items. Weight might be anything from 1 ton to 1000 tons.
By seeing in the second sheet, I need to run a lookup to fetch the FreightKey as the following.
If my items' weight is 8 ton and Mode is Air, my FreightKey should be AI20 (Because 8 tons fall in <=20 tons category so and Mode is Air so AI20
If my items' weight is 49 ton and Mode is Sea, my FreightKey should be SE60 (Because 49 tons fall in <=60 tons category so and Mode is Sea so SE60
It goes on for every item. Following is the file link to help you out.. I had tried the Vloookup and Index/Match, Multiple Index/Match but in vain.
What is the formula that I need to build in this case. Any help is greatly appreciated.
FreightKey - Download - 4shared - farida perveen
Good morning everyone here,
We have two sheets and first sheet with the following information where I need to run a VLOOKUP or INDEX MATCH to fetch the FreightKey from second sheet.
In Sheet-1
Weight | Mode | FreightKey |
<tbody>
</tbody>
4 Sea SE20
8 Rail RA20
200 Road RO200
58 Sea SE60
In Sheet-2
Weight | Mode | Freight% | FreightKey |
20 | Air | 20% | AI20 |
40 | Air | 22% | AI40 |
60 | Air | 24% | AI60 |
80 | Air | 26% | AI80 |
100 | Air | 28% | AI100 |
120 | Air | 30% | AI120 |
140 | Air | 32% | AI140 |
160 | Air | 34% | AI160 |
180 | Air | 36% | AI180 |
200 | Air | 38% | AI200 |
20 | Sea | 5% | SE20 |
40 | Sea | 6% | SE40 |
60 | Sea | 7% | SE60 |
80 | Sea | 8% | SE80 |
100 | Sea | 9% | SE100 |
120 | Sea | 10% | SE120 |
140 | Sea | 11% | SE140 |
160 | Sea | 12% | SE160 |
180 | Sea | 13% | SE180 |
200 | Sea | 14% | SE200 |
20 | Road | 8% | RO20 |
40 | Road | 9% | RO40 |
60 | Road | 10% | RO60 |
80 | Road | 11% | RO80 |
100 | Road | 12% | RO100 |
120 | Road | 13% | RO120 |
140 | Road | 14% | RO140 |
160 | Road | 15% | RO160 |
180 | Road | 16% | RO180 |
200 | Road | 17% | RO200 |
20 | Rail | 6% | RA20 |
40 | Rail | 7% | RA40 |
60 | Rail | 8% | RA60 |
80 | Rail | 9% | RA80 |
100 | Rail | 10% | RA100 |
120 | Rail | 11% | RA120 |
140 | Rail | 12% | RA140 |
160 | Rail | 13% | RA160 |
180 | Rail | 14% | RA180 |
200 | Rail | 15% | RA200 |
<tbody>
</tbody>
I have some items to be shipped. The freight will be based on the weights of the items. Weight might be anything from 1 ton to 1000 tons.
By seeing in the second sheet, I need to run a lookup to fetch the FreightKey as the following.
If my items' weight is 8 ton and Mode is Air, my FreightKey should be AI20 (Because 8 tons fall in <=20 tons category so and Mode is Air so AI20
If my items' weight is 49 ton and Mode is Sea, my FreightKey should be SE60 (Because 49 tons fall in <=60 tons category so and Mode is Sea so SE60
It goes on for every item. Following is the file link to help you out.. I had tried the Vloookup and Index/Match, Multiple Index/Match but in vain.
What is the formula that I need to build in this case. Any help is greatly appreciated.
FreightKey - Download - 4shared - farida perveen