VLookup not working.

[mrxlsx]

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

Weight

Mode

FreightKey

<tbody>
</tbody>

10 Air AI20
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

 

[Andrew Poulsom]

Formula in H2 copied down:

Excel 2010
	A	B	C	D	E	F	G	H
1	Weight	Mode	Freight%	FreightKey		Weight	Mode	FreightKey
2	20	Air	20%	AI20		10	Air	AI20
3	40	Air	22%	AI40		4	Sea	SE20
4	60	Air	24%	AI60		8	Rail	RA20
5	80	Air	26%	AI80		200	Road	RO200
6	100	Air	28%	AI100		58	Sea	SE60
7	120	Air	30%	AI120
8	140	Air	32%	AI140
9	160	Air	34%	AI160
10	180	Air	36%	AI180
11	200	Air	38%	AI200
12	20	Sea	5%	SE20
13	40	Sea	6%	SE40
14	60	Sea	7%	SE60
15	80	Sea	8%	SE80
16	100	Sea	9%	SE100
17	120	Sea	10%	SE120
18	140	Sea	11%	SE140
19	160	Sea	12%	SE160
20	180	Sea	13%	SE180
21	200	Sea	14%	SE200
22	20	Road	8%	RO20
23	40	Road	9%	RO40
24	60	Road	10%	RO60
25	80	Road	11%	RO80
26	100	Road	12%	RO100
27	120	Road	13%	RO120
28	140	Road	14%	RO140
29	160	Road	15%	RO160
30	180	Road	16%	RO180
31	200	Road	17%	RO200
32	20	Rail	6%	RA20
33	40	Rail	7%	RA40
34	60	Rail	8%	RA60
35	80	Rail	9%	RA80
36	100	Rail	10%	RA100
37	120	Rail	11%	RA120
38	140	Rail	12%	RA140
39	160	Rail	13%	RA160
40	180	Rail	14%	RA180
41	200	Rail	15%	RA200
Sheet1

Cell Formulas
Range	Formula
H2	=INDEX(D$2:D$41,MATCH(1,INDEX((A$2:A$41>=F2)*(B$2:B$41=G2),),FALSE))

 

[mrxlsx]

Andrew Poulsom,

Thank you so much for the stunning formula.

mrxlsx