Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula help (Lookup)
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
|
|||
|
|||
Instead of concatenating the cells, I like this syntax for matching multiple
columns better: =index(othersheet!$c$1:$c$10, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) Maybe you could use the same sort of technique: =INDEX(B93:E302;MATCH(1;(Pris!I2=A92:A301)*(Pris!G 2<=B92:B301);0); MATCH(Pris!H2;92:92;1)) Then you can use whatever you want in this portion: (Pris!G2<=B92:B301) (I would have guessed that column A had to be an exact match, but that's just my guess.) 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 -- Dave Peterson |
#3
|
|||
|
|||
Thank´s för your help. I´l give it a try later this afternoon. You are
right column A would be an exact match. Ola "Dave Peterson" skrev i meddelandet ... Instead of concatenating the cells, I like this syntax for matching multiple columns better: =index(othersheet!$c$1:$c$10, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) Maybe you could use the same sort of technique: =INDEX(B93:E302;MATCH(1;(Pris!I2=A92:A301)*(Pris!G 2<=B92:B301);0); MATCH(Pris!H2;92:92;1)) Then you can use whatever you want in this portion: (Pris!G2<=B92:B301) (I would have guessed that column A had to be an exact match, but that's just my guess.) 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 -- Dave Peterson |
#4
|
|||
|
|||
It worked like a charm.
Many thank´s Ola "Dave Peterson" skrev i meddelandet ... Instead of concatenating the cells, I like this syntax for matching multiple columns better: =index(othersheet!$c$1:$c$10, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) Maybe you could use the same sort of technique: =INDEX(B93:E302;MATCH(1;(Pris!I2=A92:A301)*(Pris!G 2<=B92:B301);0); MATCH(Pris!H2;92:92;1)) Then you can use whatever you want in this portion: (Pris!G2<=B92:B301) (I would have guessed that column A had to be an exact match, but that's just my guess.) 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 -- Dave Peterson |
#5
|
|||
|
|||
See if the following helps...
Assumptions: A2:A6 contains the name of the pricelist B2:B6 contains the length C1:F1 contains the weight C2:F6 contains your data Formulas: To lookup a value in Column B equal to the lookup value or next largest value less than the lookup value... =INDEX(C2:F6,LOOKUP(2,1/((A2:A6=H1)*(B2:B6=MAX((A2:A6=H1)*(B2:B6<=I1)*B2: B6))),ROW(A2:A6)-ROW(A2)+1),MATCH(J1,C1:F1,1)) or =INDEX(C2:F6,MATCH(1,((A2:A6=H1)*(B2:B6=MAX(IF((A2 :A6=H1)*(B2:B6<=I1),B2: B6)))),0),MATCH(J1,C1:F1,1)) ....confirmed with CONTROL+SHIFT+ENTER. To lookup a value in Column B equal to the lookup value or the smallest value greater than the lookup value... =INDEX(C2:F6,MATCH(1,((A2:A6=H1)*(B2:B6=MIN(IF((A2 :A6=H1)*(B2:B6=I1),B2: B6)))),0),MATCH(J1,C1:F1,1)) ....confirmed with CONTROL+SHIFT+ENTER. Note that H1 contains the name of the pricelist, I1 contains the length, and J1 contains the weight. Hope this helps! In article , "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:9 2;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 |
#6
|
|||
|
|||
Please ignore...
In article , Domenic wrote: See if the following helps... Assumptions: A2:A6 contains the name of the pricelist B2:B6 contains the length C1:F1 contains the weight C2:F6 contains your data Formulas: To lookup a value in Column B equal to the lookup value or next largest value less than the lookup value... =INDEX(C2:F6,LOOKUP(2,1/((A2:A6=H1)*(B2:B6=MAX((A2:A6=H1)*(B2:B6<=I1)*B2: B6))),ROW(A2:A6)-ROW(A2)+1),MATCH(J1,C1:F1,1)) or =INDEX(C2:F6,MATCH(1,((A2:A6=H1)*(B2:B6=MAX(IF((A2 :A6=H1)*(B2:B6<=I1),B2: B6)))),0),MATCH(J1,C1:F1,1)) ...confirmed with CONTROL+SHIFT+ENTER. To lookup a value in Column B equal to the lookup value or the smallest value greater than the lookup value... =INDEX(C2:F6,MATCH(1,((A2:A6=H1)*(B2:B6=MIN(IF((A2 :A6=H1)*(B2:B6=I1),B2: B6)))),0),MATCH(J1,C1:F1,1)) ...confirmed with CONTROL+SHIFT+ENTER. Note that H1 contains the name of the pricelist, I1 contains the length, and J1 contains the weight. Hope this helps! In article , "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 :9 2;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 |
#7
|
|||
|
|||
see my answer on your former post...
-- A+ 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 | |||
Lookup Function help | Excel Discussion (Misc queries) | |||
formula to lookup table in another Worksheet please | Excel Worksheet Functions | |||
Lookup formula | Excel Discussion (Misc queries) | |||
Can't find the right lookup formula for this | Excel Worksheet Functions | |||
Formula Question... LookUP | Excel Discussion (Misc queries) |