Formula for Vlookup between range?

[Stu Dapples]

Hi,
I have a list of about 4500 parts which I have calculated teh internal volume for. I now need to cross match this volume to a list of programs which have a range of volumes for which they are appropriate. eg:

Part volume is 0.2 litres
Program 1 range 0 - 2
Program 2 range 2.1 - 4
Program 3 range 4.1 - 6
(etc)
Can I do a look up to tell which program I need to run for each part?

Many thanks in advance,

Stu

 

[Special-K99]

Create a table 2 columns by 3 rows
0 1
2.1 2
4.1 3

Use VLOOKUP with the part volume and pull out column 2 which will tell you what Program number is

 

[taurean]

Same as Special-K99 suggestion:

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 69px"> <col style="WIDTH: 69px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 114px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0</td> <td style="BACKGROUND-COLOR: #ffff00">Program 1</td> <td> </td> <td style="BACKGROUND-COLOR: #ccccff">Volume</td> <td style="BACKGROUND-COLOR: #ccccff">Program Suitable</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2.1</td> <td style="BACKGROUND-COLOR: #ffff00">Program 2</td> <td> </td> <td style="TEXT-ALIGN: right">0.3</td> <td style="BACKGROUND-COLOR: #c0c0c0">Program 1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4.1</td> <td style="BACKGROUND-COLOR: #ffff00">Program 3</td> <td> </td> <td style="TEXT-ALIGN: right">2</td> <td style="BACKGROUND-COLOR: #c0c0c0">Program 1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td> </td> <td> </td> <td> </td> <td style="TEXT-ALIGN: right">2.3</td> <td style="BACKGROUND-COLOR: #c0c0c0">Program 2</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td> </td> <td> </td> <td> </td> <td style="TEXT-ALIGN: right">4.2</td> <td style="BACKGROUND-COLOR: #c0c0c0">Program 3</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td> </td> <td> </td> <td> </td> <td style="TEXT-ALIGN: right">4.1</td> <td style="BACKGROUND-COLOR: #c0c0c0">Program 3</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>E2</td> <td>=VLOOKUP(D2,$A$1:$B$3,2,1)</td></tr> <tr> <td>E3</td> <td>=VLOOKUP(D3,$A$1:$B$3,2,1)</td></tr> <tr> <td>E4</td> <td>=VLOOKUP(D4,$A$1:$B$3,2,1)</td></tr> <tr> <td>E5</td> <td>=VLOOKUP(D5,$A$1:$B$3,2,1)</td></tr> <tr> <td>E6</td> <td>=VLOOKUP(D6,$A$1:$B$3,2,1)</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web >> Excel Jeanie HTML 4

 

[Stu Dapples]

Job Done!

Superb gents, many thanks!

Stu