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?

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Fletcher F. Fletcher" <Fletcher F.
wrote in message ...
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?

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,B3: D3,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
B3:D3 is 25, 50, 100
B4:D6 is a range named White, with the .10, .11 and .12, etc. values in it.

A12:A14 is 6, 8.5, 10
B11:D13 is 25, 50, 100
B12:D14 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

"Fletcher F. Fletcher" <Fletcher F.
wrote in message ...
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?

Check out this link

http://www.4shared.com/file/13373896...kup_Table.html



"Fletcher F. Fletcher" wrote:

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?