Help with formula
 

Hello!
I use this formula to extract data from a pricelist. I found this formula on
the net (thanks to whoever who made it).
{=INDEX(B93:E302;MATCH(Pris!I2&Pris!G2;A92:A301&B9 2:B301;0);Match(Pris!H2;92:92;1))}
The problem I have is that it only allows exact match on the first two
lookups, I can not change the type from 0 to 1 or -1 to allow for a not
exact match like I have done in the third lookup. Column A is a name for
fifferent pricelists,Column B is length in kilometers and Row 1 is weight in
kilos. First I lokkup which pricelist to use then the length and then the
weight.
Any ideas?

My pricelist looks something like this

A B C D E F
1 2500 5000 10000 20000
2 A 20 400 500 600 700
3 A 50 500 600 700 800
4 A 100 700 800 900 1000
5 B 20 100 200 300 400
6 B 50 200 300 400 500


TIA

Ola

Hi!
Try this (my data is on Sheet2):
=INDEX(MyRange,MATCH(Pris!$G$2,MyVerticalRange,1), MATCH(Pris!$H$2,$92:$92,1)-1)

with Myrange define as (Insert/Name/Define...):
=OFFSET(Sheet2!$B$93:$IV$93,MATCH(Pris!$I$2,Sheet2 !$A$93:$A$301,0)-1,0,COUNTIF(Sheet2!$A$93:$A$301,Pris!$I$2))

and MyVerticalRanga as:
=OFFSET(Sheet2!$B$93,MATCH(Pris!$I$2,Sheet2!$A$93: $A$301,0)-1,0,COUNTIF(Sheet2!$A$93:$A$301,Pris!$I$2))

Note: that won't be able to determine a result if Pris!$G$2 is below 20 or
Pris!$H$2 is below 5000 since you don't have 0 values in row 92 or column
B....
Come back if you need more explanation...
--
CU

V.


"Ola Sigurdh" wrote:

Hello!
I use this formula to extract data from a pricelist. I found this formula on
the net (thanks to whoever who made it).
{=INDEX(B93:E302;MATCH(Pris!I2&Pris!G2;A92:A301&B9 2:B301;0);Match(Pris!H2;92:92;1))}
The problem I have is that it only allows exact match on the first two
lookups, I can not change the type from 0 to 1 or -1 to allow for a not
exact match like I have done in the third lookup. Column A is a name for
fifferent pricelists,Column B is length in kilometers and Row 1 is weight in
kilos. First I lokkup which pricelist to use then the length and then the
weight.
Any ideas?

My pricelist looks something like this

A B C D E F
1 2500 5000 10000 20000
2 A 20 400 500 600 700
3 A 50 500 600 700 800
4 A 100 700 800 900 1000
5 B 20 100 200 300 400
6 B 50 200 300 400 500


TIA

Ola