# How to get my Freight rates from two tables?

#### CopperKnife

##### New Member
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.

Thanks
CopperKnife

Last edited:

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.

Last edited:
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

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.

Try this:

Excel 2010
ABCDEFGHI
1WeightModeFreightKeyWeight ModeFreightFreightKey
25SeaSE2020Air0.08AI20
534RailRA6020Rail0.04RA20
710AirAI2030Sea0.05SE40
919SeaSE2030Rail0.06RA40

</tbody>
Sheet1

Array Formulas
CellFormula
C2{=INDEX(freightkey,MATCH(1,IF(Mode=B2,IF(weight>=A2,1)),0))}

</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
NameRefers To
freightkey=Sheet1!\$I\$2:\$I\$41
Mode=Sheet1!\$G\$2:\$G\$41
weight=Sheet1!\$F\$2:\$F\$41

</tbody>

<tbody>
</tbody>

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

Thanks.

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

Last edited:
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.

Replies
4
Views
282
Replies
2
Views
407
Replies
11
Views
736

1,196,078
Messages
6,013,302
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back