Help With Min/Max Values
 

I am trying to set up an Excel spreadsheet that will help me create
bills of material.

The problem that I have is that one of the part numbers in the BOM
changes depending on the length of the finished product (min/max range).

I have created a separate table (in tab Sheet2) that lists the min/max
ranges and what part number goes with it.

Part of this separate table looks like this:

Min. (col. A) Max. (col. B) Part Number (col. C)
------------- ------------- --------------------
0 9 70-4906-0007
10 19 70-4906-0015
20 29 70-4906-0016
30 39 70-4906-0008
40 49 70-4906-0003

So if I insert "15" in cell A1 (Sheet1), the formula would look up in
Sheet2 what part number to use and import it into my BOM. In this
example, it would be p/n 70-4906-0015 because the value "15" falls
between "10" and "19".

Because the table in Sheet2 has hundreds of entries, I'm trying not to
use an IF statement or else I'll end up with a formula that is a mile
long!

What is the most practical formula to achieve what I want to do?
--
tb

Help With Min/Max Values
 

Hi,

Am Tue, 24 Mar 2015 20:08:29 +0000 (UTC) schrieb tb:

Min. (col. A) Max. (col. B) Part Number (col. C)
------------- ------------- --------------------
0 9 70-4906-0007
10 19 70-4906-0015
20 29 70-4906-0016
30 39 70-4906-0008
40 49 70-4906-0003

insert your part numbers without hyphen and format the cells
00-0000-0000
Then you can use in B1:
=SUMPRODUCT(--(Sheet2!$A$2:$A$100<=A1),--(Sheet2!$B$2:$B$100=A1),Sheet2!$C$2:$C$100)
and format column B also
00-0000-0000


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional