Hi all

I'm trying to figure out how I can return an index to an array of values
using "MATCH".

It should be a simple MATCH function but after digging through the questions
& answers on "match" and trying using help for a couple of hours I gave up.


I have an array, {"NONE","NEGLIGIBLE","MILD","MODERATE","HIGH","SEV ERE"} and
want to match this array against the value out of a cell.

Problem is the double-quotes, as far as I can tell.

This function below works,
=MATCH("HIGH",{"NONE","NEGLIGIBLE","MILD","MODERAT E","HIGH","SEVERE"},1)

But these functions below always fail, the first is an attempt to use a
literal value, the second is an attempt to use a value out of a cell

=MATCH(CONCATENATE("""",HIGH,""""),{"NONE","NEGLIG IBLE","MILD","MODERATE","HIGH","SEVERE"},)

MATCH(CONCATENATE("""",N13,""""),{"NONE","NEGLIGIB LE","MILD","MODERATE","HIGH","SEVERE"},)

What am I doing wrong ?

goofy-duck wrote:
Hi all

I'm trying to figure out how I can return an index to an array of values
using "MATCH".

It should be a simple MATCH function but after digging through the questions
& answers on "match" and trying using help for a couple of hours I gave up.


I have an array, {"NONE","NEGLIGIBLE","MILD","MODERATE","HIGH","SEV ERE"} and
want to match this array against the value out of a cell.

Problem is the double-quotes, as far as I can tell.

This function below works,
=MATCH("HIGH",{"NONE","NEGLIGIBLE","MILD","MODERAT E","HIGH","SEVERE"},1)

But these functions below always fail, the first is an attempt to use a
literal value, the second is an attempt to use a value out of a cell

=MATCH(CONCATENATE("""",HIGH,""""),{"NONE","NEGLIG IBLE","MILD","MODERATE","HIGH","SEVERE"},)

MATCH(CONCATENATE("""",N13,""""),{"NONE","NEGLIGIB LE","MILD","MODERATE","HIGH","SEVERE"},)

What am I doing wrong ?
I don't knw why you would try to use something like the second one
instead of the first. And the third one works with

=MATCH(N13,{"NONE","NEGLIGIBLE","MILD","MODERATE", "HIGH","SEVERE"},)

Alan Beban

Hi!

Not sure what you're trying to do?

This function below works,
=MATCH("HIGH",{"NONE","NEGLIGIBLE","MILD","MODERA TE","HIGH","SEVERE"},1)

As written, that formula returns #N/A because you're using a match_type of 1
that requires the lookup_array to be sorted ascending which it is not. Try
changing the 1 to 0.

I can't figure out what you're trying to do with the concatenation
formulas???

=CONCATENATE("""","high","""")

Will return: "high" but why do that when typing "high" is easier?

But if you're using a cell reference like N13 you don't need to use quotes
in the Match formula:

=MATCH(N13,{"NONE","NEGLIGIBLE","MILD","MODERATE", "HIGH","SEVERE"},0)

Biff

"goofy-duck" wrote in message
...
Hi all

I'm trying to figure out how I can return an index to an array of values
using "MATCH".

It should be a simple MATCH function but after digging through the
questions
& answers on "match" and trying using help for a couple of hours I gave
up.


I have an array, {"NONE","NEGLIGIBLE","MILD","MODERATE","HIGH","SEV ERE"}
and
want to match this array against the value out of a cell.

Problem is the double-quotes, as far as I can tell.

This function below works,
=MATCH("HIGH",{"NONE","NEGLIGIBLE","MILD","MODERAT E","HIGH","SEVERE"},1)

But these functions below always fail, the first is an attempt to use a
literal value, the second is an attempt to use a value out of a cell

=MATCH(CONCATENATE("""",HIGH,""""),{"NONE","NEGLIG IBLE","MILD","MODERATE","HIGH","SEVERE"},)

MATCH(CONCATENATE("""",N13,""""),{"NONE","NEGLIGIB LE","MILD","MODERATE","HIGH","SEVERE"},)

What am I doing wrong ?