|
|
|
|
|
| |
 Answer |
|
| |
That sound like a vlookup might work with a table something like this
Col A Col B
0 1
5 2
10 3
15 4
20 5
and a formula like
=VLOOKUP(C1,A1:B5,2,TRUE)
This looks for a match in Col A for the value in C1 and if it doesnt find
one it returns the next highest value. i.e. 0,1,2,3,4 all return 1
Note the tabke must be sorted
Mike
"Zakynthos" wrote:
> I've been using the Match function to assign up to 7 values to variables in
> an array but need a formula that will handle up to perhaps 20 or more
> variables in this way.
>
> Say, in cell B3 I wanted to lookup the values and assign a score of '1' for
> > value x (where x is a constant), '2' for value (x+3), '3' for variable (x+17) ... etc to '25' for value (x+39)
>
> Could the Vlookup function be used to get these scores and if so what would
> be the formula to?
>
> Many thanks
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
Mike,
Many thanks for your helpful suggestion.
It certainly works OK but my problem is that I need to assign values to a
SINGLE cell (without using a table - as it will not work easily with the rest
of the Visual Basic program I've written).
Is there not something similar to nested conditional statements (max 7 only
) or Match (also 7 only?) where, with text-based variables, I can assign,
say, in cell B1 a value to the items in A1 (input via separate macro) a '1'
to the first text item that might be input to A1, '2' to the second item that
might be input to A1 .....up to '25 (or more) for the last item?
"Mike H" wrote:
> That sound like a vlookup might work with a table something like this
>
> Col A Col B
> 0 1
> 5 2
> 10 3
> 15 4
> 20 5
>
> and a formula like
> =VLOOKUP(C1,A1:B5,2,TRUE)
>
> This looks for a match in Col A for the value in C1 and if it doesnt find
> one it returns the next highest value. i.e. 0,1,2,3,4 all return 1
>
> Note the tabke must be sorted
>
> Mike
> "Zakynthos" wrote:
>
> > I've been using the Match function to assign up to 7 values to variables in
> > an array but need a formula that will handle up to perhaps 20 or more
> > variables in this way.
> >
> > Say, in cell B3 I wanted to lookup the values and assign a score of '1' for
> > > value x (where x is a constant), '2' for value (x+3), '3' for variable (x+17) ... etc to '25' for value (x+39)
> >
> > Could the Vlookup function be used to get these scores and if so what would
> > be the formula to?
> >
> > Many thanks
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
Question
Notify me of replies