Column and Row match and Index

[mat76]

Hi,

Struggling to find the answer to this one and whatever formula I try is returning an error of #N/A

I have two worksheets the first with transport data - customer,collection point, delivery point, haulier and number of pallets - all this information is in columns; the second sheet with the rates show haulier, customer, validity, delivery point in columns and then pallet numbers run across a row with rates tying in underneath. i have simplified this below.

I am just trying to reference all the data and work out what the cost for the variables would be in the example the cell i'm trying to find is H8 (320) - any help would be hugely appreciated.

I have already tried the following and several modified versions:
=INDEX('RATE LOOKUPS'!$E$6:$I$9,MATCH(1,('MASTER DATA'!A:A='RATE LOOKUPS'!C:C)*('MASTER DATA'!B:B='RATE LOOKUPS'!B:B)*('MASTER DATA'!C:C='RATE LOOKUPS'!D:D)*('MASTER DATA'!D:D='RATE LOOKUPS'!A:A)*('MASTER DATA'!E:E='RATE LOOKUPS'!E5:I5),0))

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Customer	Origin	Destination	Line	Pallets	Cost
2	WALLMART	HONG KONG	LOS ANGELES	Maersk	4	?
3
4					PALLETS
5	LINE	ORIGIN	CUSTOMER	DESTINATION	1	2	3	4	5
6	MOL	SHANGHAI	WALLMART	WASHINGTON	200	275	350	420	500
7	EVERGREEN	SHANGHAI	COSTCO	SAN DIEGO	250	325	400	470	550
8	MAERSK	HONG KONG	WALLMART	LOS ANGELES	100	175	250	320	400
9	APL	HONG KONG	COSTCO	NEW YORK	150	225	300	370	450
Sheet9

 

[Snakehips]

mat76,

Perhaps something like..

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Customer	Origin	Destination	Line	Pallets	Cost
2	WALLMART	HONG KONG	LOS ANGELES	Maersk	4	320
3
4					PALLETS
5	LINE	ORIGIN	CUSTOMER	DESTINATION	1	2	3	4	5
6	MOL	SHANGHAI	WALLMART	WASHINGTON	200	275	350	420	500
7	EVERGREEN	SHANGHAI	COSTCO	SAN DIEGO	250	325	400	470	550
8	MAERSK	HONG KONG	WALLMART	LOS ANGELES	100	175	250	320	400
9	APL	HONG KONG	COSTCO	NEW YORK	150	225	300	370	450
Sheet4

Cell Formulas
Range	Formula
F2	{=INDEX(E6:I9,MATCH(D2&B2&A2&C2,A6:A9&B6:B9&C6:C9&D6:D9,0),E2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

 

[mat76]

thanks,

have pasted directly in and checked references but returns #VALUE!, and other suggestions please? I'm on 2010 would that make a difference?

 

[lrobbo314]

On your OP you said that your data was on separate pages. The formula he gave you was for the data being on a single page. I'm guessing that you'll need to change the range references in order for the formula to work.

 

[mat76]

I used the formual on the test data first (below) and tried to replicate the result above but no luck

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Customer	Origin	Destination	Line	Pallets	Cost
2	WALLMART	HONG KONG	LOS ANGELES	MAERSK	4	#VALUE!
3
4					PALLETS
5	LINE	ORIGIN	CUSTOMER	DESTINATION	1	2	3	4	5
6	MOL	SHANGHAI	WALLMART	WASHINGTON	200.00	275.00	350.00	420.00	500.00
7	EVERGREEN	SHANGHAI	COSTCO	SAN DIEGO	250.00	325.00	400.00	470.00	550.00
8	MAERSK	HONG KONG	WALLMART	LOS ANGELES	100.00	175.00	250.00	320.00	400.00
9	APL	HONG KONG	COSTCO	NEW YORK	150.00	225.00	300.00	370.00	450.00
10
11
12	{=INDEX(E6:I9,MATCH(D2&B2&A2&C2,A6:A9&B6:B9&C6:C9&D6:D9,0),E2)}
Sheet9

 

[Snakehips]

mat76,

You need to enter just the formula without the curly braces and then confirm with Ctrl + Shift + Enter ???????

 

[mat76]

mat76,

You need to enter just the formula without the curly braces and then confirm with Ctrl + Shift + Enter ???????

Thanks Snakehips, i think it was the order of the references didn't match the order of the lookups as the curly brackets were a result of Ctrl + Shift + Enter; have also found that the below works as well having played around this morning

=INDEX(A6:I9,MATCH(A2&B2&C2&D2,C6:C9&B6:B9&D6:D9&A6:A9,0),E2+4)

really appreaciate your help