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

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

MATCH can handle many more than 7 possible values. Try it and see.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Zakynthos" wrote in message
...
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

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