Can the Match function handle more than 7 variables?

[Zakynthos]

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 H]

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

 

[Bob Phillips]

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]

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?