INDEX MATCH Query

tcarwardine

New Member
Joined
Jul 13, 2016
Messages
20
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,718
Office Version
365
Platform
Windows
Hi

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

Excel Workbook
ABCDEFGHI
1Hardware CategoryBerlinAmsterdamLondonFrankfurtCopenhagenLisbonMadridBarcelona
2117517535175135235235235
321851853515255255255255
4
5
6DestinationHardware CategoryCost
7Frankfurt2
INDEX MATCH
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,301
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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:

Forum statistics

Threads
1,077,795
Messages
5,336,373
Members
399,077
Latest member
johnk94

Some videos you may like

This Week's Hot Topics

Top