Index/Match formula help

[Ben171]

Hi, i have the following carriage cost table on a sheet called "Carriage Matrix"

The headings of each column B:E is the different type of deliveries e.g. Pallet standard, pallet next day etc.
The prices for these depends on the zones as you can see in column A.

I then have my main data sheet called "MainData". I want to use index/match to fill in Column N "Carriage Cost" depending on what delivery type is chosen in Column G and which zone is chosen in column N

Hope that is clear, I think index/match is the best way of going about this but i could be wrong. I want Column N to automatically be filled based on delivery type in Column G and zone in column M by referencing the carriage table on the sheet "Carriage Matrix" shown above

 

[kweaver]

And, what should the answers be in column N and how do you derive them?

Something like:

continents.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Zone	PALSTD	PAL-OVER	PAL2030A	PAL1200								Zone	Our Cost
2	1	35	40	55	50		PAL-OVER						1	40
3	3	38	54	58	53		PALSTD						5	70
4	5	70	84	90	85		PAL-OVER						5	84
5	NI	110	110	110	110		PALSTD						5	70
Carriage Matrix

Cell Formulas
Range	Formula
N2:N5	N2	=INDEX($A$2:$E$5,MATCH(M2,$A$2:$A$5,0),MATCH(G2,$A$1:$E$1,0))

 

[Ben171]

And, what should the answers be in column N and how do you derive them?

Hi, Sorry i should have been more clear on this.

In column N should be the carriage cost found in the carriage matrix. (screenshot 1)

E.g. If column G is PALOVER and column M is Zone 1 then in N it will be 40

If column G is PALSTD and column M is Zone 5 then it should be 70

Hope that makes sense

 

[Ben171]

And, what should the answers be in column N and how do you derive them?

Something like:

continents.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Zone	PALSTD	PAL-OVER	PAL2030A	PAL1200								Zone	Our Cost
2	1	35	40	55	50		PAL-OVER						1	40
3	3	38	54	58	53		PALSTD						5	70
4	5	70	84	90	85		PAL-OVER						5	84
5	NI	110	110	110	110		PALSTD						5	70
Carriage Matrix

Cell Formulas
Range	Formula
N2:N5	N2	=INDEX($A$2:$E$5,MATCH(M2,$A$2:$A$5,0),MATCH(G2,$A$1:$E$1,0))

This looks good, however the Carriage table is on a seperate sheet called "Carriage Matrix", and column N is on a different sheet (The main data sheet)

 

[Ben171]

Using your formula i think i have managed it with the following:

=INDEX('Carriage Matrix'!A$2:E$5,MATCH(M80,'Carriage Matrix'!A$2:A$5,0),MATCH(G80,'Carriage Matrix'!A$1:E$1,0))

Just need to check this has done it accurately