On 2002-09-19 23:05, Dorothych wrote:
Tried the formula, no luck, do you have other advice?
thanks
It would help if you had reported the result that obtains...
Let A1:C10 house the price data in Sheet2.
(1.) Select all the relevant cells in Sheet2.
(2.) Go to the Name Box on the Formula Bar.
(3.) Type PriceTable and hit enter.
In Sheet1 in C1 use one of:
[1a]
=INDEX(PriceTable,MATCH(1,(INDEX(PriceTable,0,1)=A1)*(INDEX(PriceTable,0,2)=B1),0),3)
[1b]
=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$10=A1)*(Sheet2!$B$1:$B$10=B1),0))
[2]
=INDEX(Sheet2!$C$1:$C$10,MATCH(A1&"-@-"&B1,Sheet2!$A$1:$A$10&"-@-"&Sheet2!$B$1:$B$10,0))
[3] If single record per country and price holds as Dave assumed:
=SUMPRODUCT((INDEX(PriceTable,0,1)=A1)*(INDEX(PriceTable,0,2)=B1),INDEX(PriceTable,0,3))
[1a], [1b], and [2] must be array-entered, that is, you need to hit control+shift+enter at the same time, not just enter.
Addendum. An additional method would be the following.
(1.) Insert a column before the price data in Sheet2.
(2.) In Sheet2 in A1 enter and copy down:
=B1&"-@-"&C1
(3.) Select all the relevant cells including the ones in the new column.
(4.) Go the Name Box on the Formula Bar.
(5.) Type PTable and hit enter.
Now you can use the ordinary formula in Sheet1 in C1:
=INDEX(PTable,MATCH(A1&"-@-"&B1,INDEX(PTable,0,1),0),3)
This message was edited by Aladin Akyurek on 2002-09-20 01:18