Can the Match function handle more than 7 variables?

Z

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
 
M

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
 
B

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)
 
Z

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top