how to lookup a value in a table

[chris]

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..

 

[JMB]

check here:
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.

 

[JMB]

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))

 

[chris]

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]

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

=INDEX(C27,MATCH(1,(A2:A7=A11)*(B2:B7=B11),0),MATCH(C11,C11))

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

 

[chris]

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