Results from more than one criteria

[Formatting challenges in frontpage]

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

 

[T. Valko]

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?


--
Biff
Microsoft Excel MVP


"Formatting challenges in frontpage"

 

[Formatting challenges in frontpage]

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

 

[T. Valko]

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.

--
Biff
Microsoft Excel MVP


"Formatting challenges in frontpage"