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

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

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

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

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

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

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