I have a spreadsheet of data set up in excel something like this

UK France Spain
5kg 5.00 10.00 15.00
10kg 6.00 12.00 16.00
15kg 7.00 13.00 16.25
20kg 8.00 15.00 16.50

I want to be able to type in a cell the county name and then in another cell
the weight, and then create a formaula which gives me the price from the
table above.

ie if i want france at 15kg... then the answer would be 13.00

Is this possible? any help would be really appreciated

F2 contains weight (I ignored units) and F1 country. Change ranges to suit

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(F1,$B $1:$D$1,0))

A B C D
UK France Spain
5 5.00 10.00 15.00 '<=== Row 2
10 6.00 12.00 16.00
15 7.00 13.00 16.25
20 8.00 15.00 16.50




"Kerry" wrote:

I have a spreadsheet of data set up in excel something like this

UK France Spain
5kg 5.00 10.00 15.00
10kg 6.00 12.00 16.00
15kg 7.00 13.00 16.25
20kg 8.00 15.00 16.50

I want to be able to type in a cell the county name and then in another cell
the weight, and then create a formaula which gives me the price from the
table above.

ie if i want france at 15kg... then the answer would be 13.00

Is this possible? any help would be really appreciated

I saw something on how to do this not long ago in "This isn't Excel, it's
Magic"

I'm assuming you have your data in cells A1 to D5 for this example
Select B1 (UK) to D5 (16.5)
Insert - Name - Create
Select Top Row

Now select A2 (5kg) to D5 (16.5)
Insert - Name - Create
Select LEFT column

To find the intersection enter
=_15kg France

The _15kg and France will be named ranges.

"Kerry" wrote:

I have a spreadsheet of data set up in excel something like this

UK France Spain
5kg 5.00 10.00 15.00
10kg 6.00 12.00 16.00
15kg 7.00 13.00 16.25
20kg 8.00 15.00 16.50

I want to be able to type in a cell the county name and then in another cell
the weight, and then create a formaula which gives me the price from the
table above.

ie if i want france at 15kg... then the answer would be 13.00

Is this possible? any help would be really appreciated

Adding to Barb's response you could use the following in your cell:

=INDIRECT("_"& F2 & " " & F1)

Where F2 = weight (15Kg)
F1= country (France)

HTH

"Barb Reinhardt" wrote:

I saw something on how to do this not long ago in "This isn't Excel, it's
Magic"

I'm assuming you have your data in cells A1 to D5 for this example
Select B1 (UK) to D5 (16.5)
Insert - Name - Create
Select Top Row

Now select A2 (5kg) to D5 (16.5)
Insert - Name - Create
Select LEFT column

To find the intersection enter
=_15kg France

The _15kg and France will be named ranges.

"Kerry" wrote:

I have a spreadsheet of data set up in excel something like this

UK France Spain
5kg 5.00 10.00 15.00
10kg 6.00 12.00 16.00
15kg 7.00 13.00 16.25
20kg 8.00 15.00 16.50

I want to be able to type in a cell the county name and then in another cell
the weight, and then create a formaula which gives me the price from the
table above.

ie if i want france at 15kg... then the answer would be 13.00

Is this possible? any help would be really appreciated

Thanks toppers,

Kerry

That should probably be:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,1),MATCH(F1,$B $1:$D$1,0))

Thus: The match-type in the first MATCH bit should be set to 1, not to 0.

Toppers wrote:
F2 contains weight (I ignored units) and F1 country. Change ranges to suit

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(F1,$B $1:$D$1,0))

A B C D
UK France Spain
5 5.00 10.00 15.00 '<=== Row 2
10 6.00 12.00 16.00
15 7.00 13.00 16.25
20 8.00 15.00 16.50




"Kerry" wrote:


I have a spreadsheet of data set up in excel something like this

UK France Spain
5kg 5.00 10.00 15.00
10kg 6.00 12.00 16.00
15kg 7.00 13.00 16.25
20kg 8.00 15.00 16.50

I want to be able to type in a cell the county name and then in another cell
the weight, and then create a formaula which gives me the price from the
table above.

ie if i want france at 15kg... then the answer would be 13.00

Is this possible? any help would be really appreciated