# Index & Match

#### AndyMax

##### Board Regular
<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--> My tariff from a supplier look similar to example below.

Column A is zipcode
B1 to H1 is amount of kg
B3 to H5 is the rate

Example: Shipment of total 55 kg to zipcode 1017 will cost me € 50

 Zipcode 0-100kg 101-500kg 501-889kg 890-999kg 1015-1018 50 60 80 110 1019-2510 60 70 90 120 2511-2893 40 110 124 130

<tbody>
</tbody>

I get following information on the invoice from the supplier. Can be a lot of shipments….

 Zipcode Amount of kg Price Export or Import 2015 400 70 SE 2560 842 170 SI

<tbody>
</tbody>

I use following formula to get the right price and it works great.
=INDEX(\$B\$2:\$E\$4,MATCH(A8,\$A\$2:\$A\$4,1),MATCH(B8,\$B\$1:\$E\$1,1))

The problem is that the shipment goes from A to B (Export=SE) and sometimes from B to A (Import=SI)

There is total different price if the shipment is an export or import but my formula does not take this in consideration.

Can anyone help me out here?

BR Andy

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### AndyMax

##### Board Regular
Hi Guys!

Use to be many quick answear here

Maybe I was a bit unclear?

Br

Andy

#### BiocideJ

##### Well-known Member
Your request is in-fact a bit unclear. I am confused as to where the table is for SI vs. a table for SE. From how you worded your request, I assume there must be two separate tables.

If this is the case, you would have to have an IF statement that determines which lookup table to use...

=IF(D8="SE",INDEXMATCH(SE_table), INDEXMATCH(SI_table))

#### AndyMax

##### Board Regular
I will try to be more clear in my question.

If A2 contains SE I want to search to be done in column B (Zipcode 1). A3 contains SI serach to be done in column C (Zipcode 2)

I want to combine both formula below in to one with an IF statement. How do I do this setup?

Export

Import

 Export or Import Zipcode 1 Zipcode 2 Price SE 2015 1400 70 SI 2560 1800 170

<tbody>
</tbody>

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,650
Messages
5,838,566
Members
430,556
Latest member
Peachforyou

### 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