INDEX MATCH Query

[tcarwardine]

Hi

I think this may be an Index/Match formula but not 100%

Trying to work out a cost based on two factors as displayed below.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Hardware Category
[/TD]
[TD]Berlin
[/TD]
[TD]Amsterdam
[/TD]
[TD]London
[/TD]
[TD]Frankfurt
[/TD]
[TD]Copenhagen
[/TD]
[TD]Lisbon
[/TD]
[TD]Madrid
[/TD]
[TD]Barcelona
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]£175
[/TD]
[TD]£175
[/TD]
[TD]£35
[/TD]
[TD]£175
[/TD]
[TD]£135
[/TD]
[TD]£235
[/TD]
[TD]£235
[/TD]
[TD]£235
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]£185
[/TD]
[TD]£185
[/TD]
[TD]£35
[/TD]
[TD]£15
[/TD]
[TD]£255
[/TD]
[TD]£255
[/TD]
[TD]£255
[/TD]
[TD]£255
[/TD]
[/TR]
</tbody>[/TABLE]

Then I have a table to with drop downs to select which Hardware Category and which City for delivery:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Destination
[/TD]
[TD]Hardware Category
[/TD]
[TD]Cost
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am having trouble populating the Cost cell (C2)

Thanks in advance

 

[Peter_SSs]

Hi

I think this may be an Index/Match formula but not 100%

You are correct.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Hardware Category	Berlin	Amsterdam	London	Frankfurt	Copenhagen	Lisbon	Madrid	Barcelona
2	1	175	175	35	175	135	235	235	235
3	2	185	185	35	15	255	255	255	255
4
5
6	Destination	Hardware Category	Cost
7	Frankfurt	2	15
INDEX MATCH

 

[Special-K99]

Adjust the formula as necessary

Sheet1!A1 is "Hardware Category"
Sheet2!A1 is "Destination"

in Sheet2!C2
=INDEX(Sheet1!B2:I3,MATCH(A2,Sheet1!B$1:I$1,0),MATCH(B2,Sheet1!A$2:A$3,0))

 

[Gerald Higgins]

Maybe something like this.

Lets assume your first table is in the range A4:I6.

In C2

=OFFSET($A$4,MATCH($B$2,$A$5:$A$6,0),MATCH($A$2,$B$4:$I$4,0),1,1)

There are other ways of doing this as well.

 

[tcarwardine]

perfect. thanks very much

 

[Peter_SSs]

perfect. thanks very much

You're welcome.