How to determine a value from an associated range?

[Badmojoman1977]

Hi all,

I've seen a few flavors of this question asked, but not quite what I'm looking to do (maybe I missed it somewhere).

I have a table that contains a column with low value, a column that contains high values, and a column with an associated text field. Here's an example:

Low High Name
0 10000 John
10001 20000 David
20001 30000 Mike
30001 40000 Steve
...
100001 200000 Frank



On a separate sheet, I have a column of values. What I'm looking to do is, for each value, determine which range it falls in and pull in the associated name.

For example:

Value Associated Name
20983 Mike
932 John
39281 Steve

How can I create a formula or VBA module to pull in the associated names?

Thanks in advance!

 

[Michael M]

Maybe something like

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Low</td><td style=";">High</td><td style=";">Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">10000</td><td style=";">John</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">10001</td><td style="text-align: right;;">20000</td><td style=";">David</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">20001</td><td style="text-align: right;;">30000</td><td style=";">Mike</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">30001</td><td style="text-align: right;;">40000</td><td style=";">Steve</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">100001</td><td style="text-align: right;;">200000</td><td style=";">Frank</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">20393</td><td style=";">Mike</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=LOOKUP(<font color="Blue">A8,{0,10000,20000,30000,40000,200000},{"John","David","Mike","steve","Frank"}</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

Hi all,

I've seen a few flavors of this question asked, but not quite what I'm looking to do (maybe I missed it somewhere).

I have a table that contains a column with low value, a column that contains high values, and a column with an associated text field. Here's an example:

Low High Name
0 10000 John
10001 20000 David
20001 30000 Mike
30001 40000 Steve
...
100001 200000 Frank



On a separate sheet, I have a column of values. What I'm looking to do is, for each value, determine which range it falls in and pull in the associated name.

For example:

Value Associated Name
20983 Mike
932 John
39281 Steve

How can I create a formula or VBA module to pull in the associated names?

Thanks in advance!

Try this...

With your table on Sheet2 in the range A2:C5...

On some other sheet:

A2 = some number

Enter this formula in B2:

=VLOOKUP(A2,Sheet2!A$2:C$5,3)

Copy down as needed.

 

[Badmojoman1977]

Thanks guys - I wasn't clear enough here:

I have over 200 rows identifying ranges, so to write a formula to check each row option is going to be too complex.

Is there a programmatic way to accomplish what I'm looking to do?

 

[Michael M]

In both of our examples they can simply be copied to the end of the column, particularly the version Biff has posted, which I would recommend at this stage ?
Obviously you would have to change the ranges within the formulae

As an example using Biffs formula on the "same" sheet
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Low</td><td style=";">High</td><td style=";">Name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">10000</td><td style=";">John</td><td style="text-align: right;;">15250</td><td style=";">David</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">10001</td><td style="text-align: right;;">20000</td><td style=";">David</td><td style="text-align: right;;">23911</td><td style=";">Mike</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">20001</td><td style="text-align: right;;">30000</td><td style=";">Mike</td><td style="text-align: right;;">45101</td><td style=";">Steve</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">30001</td><td style="text-align: right;;">40000</td><td style=";">Steve</td><td style="text-align: right;;">1500</td><td style=";">John</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">100001</td><td style="text-align: right;;">200000</td><td style=";">Frank</td><td style="text-align: right;;">37000</td><td style=";">Steve</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D2,A$2:C$5,3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D3,A$2:C$5,3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D4,A$2:C$5,3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D5,A$2:C$5,3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D6,A$2:C$5,3</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

Thanks guys - I wasn't clear enough here:

I have over 200 rows identifying ranges, so to write a formula to check each row option is going to be too complex.

Is there a programmatic way to accomplish what I'm looking to do?

In the grand scheme of things in Excel, that's a fairly simple formula and needing it in 200 rows is not a big deal.

Maybe someone will check in with VBA code to do it.

Good luck!

 

[Badmojoman1977]

Hi Bill -- Sorry, missed your note. I think that worked, but I'm not sure why?! I'm familiar with VLOOKUP, but I guess I didn't realize what the "TRUE" match option did?

In any case, thanks so much!

 

[Peter_SSs]

Low High Name
0 10000 John
10001 20000 David
20001 30000 Mike
30001 40000 Steve
...
100001 200000 Frank

Michael, I think you may have missed that the OP omitted (probably quite a few) rows from the lookup table, so putting all the missing values in your formula structure would make a very long formula.

 

[T. Valko]

Hi Bill -- Sorry, missed your note. I think that worked, but I'm not sure why?! I'm familiar with VLOOKUP, but I guess I didn't realize what the "TRUE" match option did?

In any case, thanks so much!

The TRUE match option means that you want an "approximate" match if an exact match is not found and, this is the important thing, the table array is sorted in ascending order on the leftmost column.

Thanks for the feedback!

 

[Michael M]

Thanks Peter
I did pick that up, that's why I recommended Biffs approach in preference to mine.