# INDEX MATCH Query

#### tcarwardine

##### New Member
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)

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Peter_SSs

##### MrExcel MVP, Moderator
Hi

I think this may be an Index/Match formula but not 100%
You are correct.

Excel Workbook
ABCDEFGHI
2117517535175135235235235
321851853515255255255255
4
5
6DestinationHardware CategoryCost
7Frankfurt215
INDEX MATCH

Last edited:

#### Special-K99

##### Well-known Member

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

##### Well-known Member
Maybe something like this.

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

In C2
Code:
``=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.

Last edited:

#### tcarwardine

##### New Member
perfect. thanks very much