Checking for a values in a range

[logout151]

I have numbers that need to be assigned a value based on what range they are in and I do not know what function to use.

E.g.

SourceData
1
12

Range RangeKeyWord
1-10 testa
11-20 testb

What I would like to do, is pick up the corresponding RangeKeyWord for each piece of source data.

Here's the kicker, I can't use an IF, because there are over 10,000 ranges and the if statement would be huge.

Any Ideas?

 

[Dreamboat]

If you're serious about the multiples of 10, use this instead:

=VLOOKUP(ROUNDUP(A2,-1),$F$1:$G$3,2)


Where F1:G3 is your data range (you can name the range if you want and just put the name of it instead of the absolute references), and 2 is the number of the column (2nd) that you want to return from the data range.

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[logout151]

Unfortunately, it is not in multiples of 10....

 

[Dreamboat]

Is this a once and done? And what ARE the multiples, if known?

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[logout151]

No, it not a once and done. And the range's can change. I could easily do this in vba, however the person I am doing this for want's it done by formula.

 

[Hasit]

Practically impossible without VBA I would say. I considered this through formula, but as you have already said, the formula would be huge, unwieldy and pretty impossible to manage as things change in your ranges.

 

[bryanbayfield]

Let me get this straight ... you've got an array of numbers ... you want to assign a code to an adjacent cell for each of the members of this array dependent upon their location (ie row)?

A simple solution would be to have a sheet (call it lookup) within your workbook containing one column which specifies the first row of a range for which a particular value should be assigned and another column which specifies what that value is.

Say the sheet you've got that needs working out is called Test.

Sub sortit()
dim n as integer
set lk = sheets("lookup&quot
for each cll in sheets("Test&quot.range("f1",range("f1&quot.end(xldown)).cells
n = cll.row
cll.range("b2&quot.value = application.worksheetfunctions.lookup(n, lk.range("a1",range("a1&quot.end(xldown)), lk.range("a1",range("a1&quot.end(xldown)).offset(0,1))
next cll
end sub

You'd also want some way of putting in the last date that sortit was run (put it at the end of the sortit macro - lk.range(whatever).value = format(now(),"dd/mm/yy&quot), and also the last time that the lookup sheet was edited (activesheet.range(whatever2) = format(now(), "dd/mm/yy&quot, linked to the change event of the lookup worksheet).

This would seem the simplest way of adjusting your range of values for which your assigning values as well as making sure that you keep the worksheet up to date.

 

[DaleWatson123321]

logout151,

Dreamboat was on the right track, with use of the VLOOKUP function.

I've created an example file which WORKS COMPLETELY, according to all your specified needs - for example with varying ranges.

On "Sheet1", I have the following:

1 A B
2 1 =VLOOKUP(A2,table,2)
3 12 =VLOOKUP(A3,table,2)

On "Sheet2", I have the following "table" (the table is "Range-Named" as "table".

1 B C
2 1 testa
3 10 testa
4 11 testb
5 20 testb
6 21 testc
7 25 testc
8 26 testd
9 27 testd
10 28 teste
11 40 teste

You can see that the range for "testc" is 21-25, and the range for "testd" is 26-27.

If and when a range needs to be changed, you only have to go to the table and revise the numbers in column "B".

If you would like a copy of the file, just email me and I'll return a copy.

Hope this resolves your task. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca