i have a table matrix of garments by sizes for the same style going down the
column
across the columns are difference prices based on qty desire i know how to
look up the style only but how do you lookup the style and a size at the same
time based on qty desire.
ex.
the price for xl sizes are different than 2xl sizes for the same garment..

check he
http://www.cpearson.com/Excel/lookups.htm#DoubleLookup

You could also do it with the Index function (see XL help for description).
=INDEX(YourTable, Match(Size, SizeColumn, 0), Match(Qty, Qty Column,0))

Match is capable of exact match and approximate match lookups. I would
guess one price covers a range of quantity so you probably will want to check
help for a description of how Match works.

"chris" wrote:

i have a table matrix of garments by sizes for the same style going down the
column
across the columns are difference prices based on qty desire i know how to
look up the style only but how do you lookup the style and a size at the same
time based on qty desire.
ex.
the price for xl sizes are different than 2xl sizes for the same garment..

To elaborate, based on the details provided in your other posts, try setting
the table up like

A B C D E
style size 1 6
P660 XL Colors 37.12 35.87
P660 2XL Colors 41.98 40.93
P660 3XL Colors 45.98 44.83

and lets say this table is in A1:E4 and A8 contains the size to look up and
B8 has the quantity to look up. This formula should work for you.

=INDEX($D$2:$E$4,MATCH(A8,$B$2:$B$4,0),MATCH(B8,$D $1:$E$1))



"JMB" wrote:

check he
http://www.cpearson.com/Excel/lookups.htm#DoubleLookup

You could also do it with the Index function (see XL help for description).
=INDEX(YourTable, Match(Size, SizeColumn, 0), Match(Qty, Qty Column,0))

Match is capable of exact match and approximate match lookups. I would
guess one price covers a range of quantity so you probably will want to check
help for a description of how Match works.

"chris" wrote:

i have a table matrix of garments by sizes for the same style going down the
column
across the columns are difference prices based on qty desire i know how to
look up the style only but how do you lookup the style and a size at the same
time based on qty desire.
ex.
the price for xl sizes are different than 2xl sizes for the same garment..

in my table there are other products, i need to find the product first then
the price based on total qty and then the sizes to apply the lookedup
prices.... i do understand the match function.. i am missing something..

"JMB" wrote:

To elaborate, based on the details provided in your other posts, try setting
the table up like

A B C D E
style size 1 6
P660 XL Colors 37.12 35.87
P660 2XL Colors 41.98 40.93
P660 3XL Colors 45.98 44.83
P661
P661
p785
etc
and lets say this table is in A1:E4 and A8 contains the size to look up and
B8 has the quantity to look up. This formula should work for you.

=INDEX($D$2:$E$4,MATCH(A8,$B$2:$B$4,0),MATCH(B8,$D $1:$E$1))



"JMB" wrote:

check he
http://www.cpearson.com/Excel/lookups.htm#DoubleLookup

You could also do it with the Index function (see XL help for description).
=INDEX(YourTable, Match(Size, SizeColumn, 0), Match(Qty, Qty Column,0))

Match is capable of exact match and approximate match lookups. I would
guess one price covers a range of quantity so you probably will want to check
help for a description of how Match works.

"chris" wrote:

i have a table matrix of garments by sizes for the same style going down the
column
across the columns are difference prices based on qty desire i know how to
look up the style only but how do you lookup the style and a size at the same
time based on qty desire.
ex.
the price for xl sizes are different than 2xl sizes for the same garment..

You want to look up the price based on style, size, and quantity? Assuming
the table in my previous post is in A1:D7 (row 1 is headers, ColA is style,
ColB is size, and the price data is in C2:D7), A11=the style to look up,
B11=the size to look up, and C11=the quantity to look up, then:

=INDEX(C2:D7,MATCH(1,(A2:A7=A11)*(B2:B7=B11),0),MA TCH(C11,C1:D1))

array entered using Cntrl+Shift+Enter or you'll get #VALUE.



"chris" wrote:

in my table there are other products, i need to find the product first then
the price based on total qty and then the sizes to apply the lookedup
prices.... i do understand the match function.. i am missing something..

"JMB" wrote:

To elaborate, based on the details provided in your other posts, try setting
the table up like

A B C D E
style size 1 6
P660 XL Colors 37.12 35.87
P660 2XL Colors 41.98 40.93
P660 3XL Colors 45.98 44.83
P661
P661
p785
etc
and lets say this table is in A1:E4 and A8 contains the size to look up and
B8 has the quantity to look up. This formula should work for you.

=INDEX($D$2:$E$4,MATCH(A8,$B$2:$B$4,0),MATCH(B8,$D $1:$E$1))



"JMB" wrote:

check he
http://www.cpearson.com/Excel/lookups.htm#DoubleLookup

You could also do it with the Index function (see XL help for description).
=INDEX(YourTable, Match(Size, SizeColumn, 0), Match(Qty, Qty Column,0))

Match is capable of exact match and approximate match lookups. I would
guess one price covers a range of quantity so you probably will want to check
help for a description of how Match works.

"chris" wrote:

i have a table matrix of garments by sizes for the same style going down the
column
across the columns are difference prices based on qty desire i know how to
look up the style only but how do you lookup the style and a size at the same
time based on qty desire.
ex.
the price for xl sizes are different than 2xl sizes for the same garment..

thanks a bunch
the formula works
my other problem was the data had unprinted chars in the fields and i could
get it to work in the beginning. after i clean up the file , everything work
out
again thank you thank you
chris

"JMB" wrote:

You want to look up the price based on style, size, and quantity? Assuming
the table in my previous post is in A1:D7 (row 1 is headers, ColA is style,
ColB is size, and the price data is in C2:D7), A11=the style to look up,
B11=the size to look up, and C11=the quantity to look up, then:

=INDEX(C2:D7,MATCH(1,(A2:A7=A11)*(B2:B7=B11),0),MA TCH(C11,C1:D1))

array entered using Cntrl+Shift+Enter or you'll get #VALUE.



"chris" wrote:

in my table there are other products, i need to find the product first then
the price based on total qty and then the sizes to apply the lookedup
prices.... i do understand the match function.. i am missing something..

"JMB" wrote:

To elaborate, based on the details provided in your other posts, try setting
the table up like

A B C D E
style size 1 6
P660 XL Colors 37.12 35.87
P660 2XL Colors 41.98 40.93
P660 3XL Colors 45.98 44.83
P661
P661
p785
etc
and lets say this table is in A1:E4 and A8 contains the size to look up and
B8 has the quantity to look up. This formula should work for you.

=INDEX($D$2:$E$4,MATCH(A8,$B$2:$B$4,0),MATCH(B8,$D $1:$E$1))



"JMB" wrote:

check he
http://www.cpearson.com/Excel/lookups.htm#DoubleLookup

You could also do it with the Index function (see XL help for description).
=INDEX(YourTable, Match(Size, SizeColumn, 0), Match(Qty, Qty Column,0))

Match is capable of exact match and approximate match lookups. I would
guess one price covers a range of quantity so you probably will want to check
help for a description of how Match works.

"chris" wrote:

i have a table matrix of garments by sizes for the same style going down the
column
across the columns are difference prices based on qty desire i know how to
look up the style only but how do you lookup the style and a size at the same
time based on qty desire.
ex.
the price for xl sizes are different than 2xl sizes for the same garment..