Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |