Lookup in Tables

[Fletcher F. Fletcher]

I want to return a single value based on user inputs. The user will put in a
size, material & # of colors. I was using nested hlookups with named ranged
for the tables, but I cant figure out how to use more than one table in the
lookup.

My tables are setup like this:

White
QTY
Size 25 50 100
6 .10 .11 .12
8.5 .13 .12 .11
10 .15 .14 .13

Black
QTY
Size 25 50 100
6 .11 .12 .13
8.5 .14 .13 .12
10 .16 .15 .14

So if the user entered black, size 6 and a quantity of 25, they would get .11

Any suggestions of how to do this?

 

[Don Guillett]

Do you want user to input in 1 or 3 cells? Formulas or a macro?
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.

 

[L. Howard Kittle]

Hi FFF,

A bit long winded to 'splain the set up but it works pretty well. Here is a
sample to get you going on your real data.

=INDEX(INDIRECT(E1),MATCH(A1,A4:A6,0),MATCH(B1,B33,0))

A1 has a drop down list of 6, 8.5, 10
B1 has a drop down list of 25, 50, 100
E1 has a drop down list of Black, White.

A4:A6 is 6, 8.5, 10
B33 is 25, 50, 100
B46 is a range named White, with the .10, .11 and .12, etc. values in it.

A12:A14 is 6, 8.5, 10
B1113 is 25, 50, 100
B1214 is a named range Black, with the .11, .12 and .13, etc. values in
it.

Select a size in A1 drop down.
Select a quanity in B1 drop down.
Select a color in E1 drop down.

So if you selected Black, 10, 100, the cell with the Index-Match formula
should return .14 per your data set.

Need More help, post back.

HTH
Regards,
Howard

 

[Teethless mama]

Check out this link

http://www.4shared.com/file/133738968/b4b8df91/Lookup_Table.html