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.

Hardware Category	Berlin	Amsterdam	London	Frankfurt	Copenhagen	Lisbon	Madrid	Barcelona
1	£175	£175	£35	£175	£135	£235	£235	£235
2	£185	£185	£35	£15	£255	£255	£255	£255

<tbody>
</tbody>

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

Destination	Hardware Category	Cost

<tbody>
</tbody>

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.