How to get my Freight rates from two tables?

[CopperKnife]

Hello all,

I have two tables like the following.

Table-1

Weight	Mode	FreightKey
10	Road	??????
8	Rail	??????
45	Sea	???????
5	Air	??????
23	Sea	??????

<tbody>
</tbody>

Table-2

Weight	Mode	FreightRate	FreightKey
20	Rail	6%	RA20
30	Road	4%	RO40
40	Air	20%	AI40
50	Sea	8%	SE60
60	Road	12%	RO60

<tbody>
</tbody>

I need to get the FreightKey from the second table to the first table.

I tried Index/Match with less than, greater than choices also Vlookup with approximate match. But I could not get the correct result.

I need to get the FreightKey according to their Weight and Mode of transport.

Please check the following link to get the sample work book.

https://drive.google.com/file/d/0B8-z_iZazKR1MFB1cDUxdkc4YkU/view?usp=sharing

Thanks
CopperKnife

 

[SpillerBD]

You need to Sort Table2 Descending for Columns I (Weight) & J (Mode).
Then use the array formula (enter with CTRL+SHIFT+ENTER.)
=INDEX($L$2:$L$41,MATCH(A2&B2,$I$2:$I$41&$J$2:$J$41,1))

Dang, it looked right and I was waiting on Excel to become unbusy. Went back to check after posting, and it wasn't.

 

[CopperKnife]

Hi,
Its already sorted. If you use "1", in the MATCH function as the last argument it gives you exact match, which in this case will throw up #N/A errors, SpilerBD !

Any help from any body??!
CopperKnife

 

[mick0005]

What is your criteria? For a weight of 5, does that round up to 10, or are you looking for it to select a return value that is greater than or equal to 5? i.e. 20, since 20 is the smallest weight in your 2nd table.

 

[mick0005]

Try this:

Excel 2010

	A	B	C	D	E	F	G	H	I
1	Weight	Mode	FreightKey			Weight	Mode	Freight	FreightKey
2	5	Sea	SE20			20	Air	0.08	AI20
3	10	Road	RO20			20	Sea	0.03	SE20
4	13	Rail	RA20			20	Road	0.06	RO20
5	34	Rail	RA60			20	Rail	0.04	RA20
6	6	Road	RO20			30	Air	0.1	AI40
7	10	Air	AI20			30	Sea	0.05	SE40
8	69	Air	AI120			30	Road	0.08	RO40
9	19	Sea	SE20			30	Rail	0.06	RA40

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C2 {=INDEX(freightkey,MATCH(1,IF(Mode=B2,IF(weight>=A2,1)),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To freightkey =Sheet1!$I$2:$I$41 Mode =Sheet1!$G$2:$G$41 weight =Sheet1!$F$2:$F$41 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[CopperKnife]

Yeah, this is what I want, mick0005. You are great.

Thanks.

 

[mick0005]

Yeah, this is what I want, mick0005. You are great.

Thanks.

Took me awhile... was overthinking if there was a way to have two match types... 1 being exact and 1 being greater than or less than. Figured out instead it just needed to be built into the logic of the match itself.

I actually just saved this one into my personal workbook for my own future reference

 

[CopperKnife]

Hey mick0005,
Check this out, by another Excel Ninja on ExcelForum.Com for the same query.

How to get my Freight rates from two tables?

CopperKnife.

 

[mick0005]

Hey mick0005,
Check this out, by another Excel Ninja on ExcelForum.Com for the same query.

How to get my Freight rates from two tables?

CopperKnife.

Just took a look at that... another interesting way to do it. I considered a lookup table of sorts. I think the solution I gave you is a bit easier, but its good to have both.

Enjoy.