Results from more than one criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Formatting challenges in frontpage - 27 May 2008 18:32 GMT

I am trying to do a selection formula. It is for hardware. I have a
workbook set up where I have different tabs, and I need a part number to
return based upon both size and thickness that is stated by the user.

For example:

Hardware size: 4
Thickness: .231

I need to have the formula (with index and match) find the part number that
will match both of these criteria. I have used index and can get it to
return the first, but not the second.

Thanks, Nancy

Reply to this Message

T. Valko - 27 May 2008 21:30 GMT

It would help if you provided more details.

Like:

Where are we supposed to look for Hardware size: 4 and Thickness: .231

Are you looking for *exact* matches or approximate matches? If you're
looking for approximate matches do you wnat the closest match that is
greater than or less than the criteria?

Signature

Biff
Microsoft Excel MVP

>I am trying to do a selection formula. It is for hardware. I have a
> workbook set up where I have different tabs, and I need a part number to
[quoted text clipped - 11 lines]
>
> Thanks, Nancy

Reply to this Message

Formatting challenges in frontpage - 27 May 2008 21:44 GMT

Hi,

So this is going to be a selector guide if I can get it working. There will
be user input, for the size of the hardware, type, etc. As it gets the size
and type of hardware, I have it set up to get the thickiness of the hardware
through vlookups used on the workbook. As all the nut, washer, etc is
selected, it will tabulate the total thickness of all of the items together.
I then need to find the screw part number that will be long enough to
accomodate the thickness, so I need the next highest length. For example if
I have .20 and .25 length screws and it tabulates to .231, then I would need
it to get the .25 part number.

Here is an example of how the data is set up: This is the list of screw
information.

Size    Type    Series    Length    Part Number
0.250    Pan Head    UNF    0.312    ms51958-76
0.250    Pan Head    UNF    0.375    ms51958-77
0.250    Pan Head    UNF    0.438    ms51958-78
0.250    Pan Head    UNF    0.500    ms51958-79
0.250    Pan Head    UNF    0.625    ms51958-80
0.250    Pan Head    UNF    0.750    ms51958-81
0.250    Pan Head    UNF    0.875    ms51958-82
0.250    Pan Head    UNF    1.000    ms51958-83
0.250    Pan Head    UNF    1.250    ms51958-84
0.250    Pan Head    UNF    1.500    ms51958-85
0.250    Pan Head    UNF    1.750    ms51958-86
0.250    Pan Head    UNF    2.000    ms51958-87
0.250    Pan Head    UNF    2.250    ms51958-88
0.250    Pan Head    UNF    2.500    ms51959-89
2    Pan Head    UNC    0.125    ms51957-1
2    Pan Head    UNC    0.188    ms51957-2
2    Pan Head    UNC    0.250    ms51957-3
2    Pan Head    UNC    0.312    ms51957-4
2    Pan Head    UNC    0.375    ms51957-5
2    Pan Head    UNC    0.438    ms51957-6
2    Pan Head    UNC    0.500    ms51957-7
2    Pan Head    UNC    0.625    ms51957-8
2    Pan Head    UNC    0.750    ms51957-9
2    Pan Head    UNC    0.875    ms51957-10
4    Pan Head    UNC    0.125    ms51957-11
4    Pan Head    UNC    0.188    ms51957-12
4    Pan Head    UNC    0.250    ms51957-13
4    Pan Head    UNC    0.312    ms51957-14
4    Pan Head    UNC    0.375    ms51957-15
4    Pan Head    UNC    0.438    ms51957-16
4    Pan Head    UNC    0.500    ms51957-17

This is the user selection information (there are drop downs in the actual
workbook)
Hardware Type    Pan Head
Hardware Size    8
First Part Thickness    0.125
Second Part Thickness    0.188
Flat Washer Type    Standard
Flat Washer Thickness (X2)    0.036
Lock Washer Type    External
Lock Washer Thickness    0.046
Nut Type    Plain
Nut Thickness    0.130

Stackup Height    0.561

I hope that is enough detail. The issue is that it has to be the same size
and the right thickness, so there are two criteria to determine what the
right part number is.

Thanks, Nancy

> It would help if you provided more details.
>
[quoted text clipped - 21 lines]
> >
> > Thanks, Nancy

Reply to this Message

T. Valko - 27 May 2008 22:52 GMT

Well, I'd have to see this in front of me to make heads or tails out of it.

If your file is <1mb in size and doesn't contain any VBA code I'll take a
look if you want to send a copy to me. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Signature

Biff
Microsoft Excel MVP

> Hi,
>
[quoted text clipped - 98 lines]
>> >
>> > Thanks, Nancy

Reply to this Message