Trying to MATCH a value out of a cell on an ARRAY - help pls

[Guest]

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","SEVERE"} 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","MODERATE","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","NEGLIGIBLE","MILD","MODERATE","HIGH","SEVERE"},)

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

What am I doing wrong ?

 

[Alan Beban]

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","SEVERE"} 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","MODERATE","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","NEGLIGIBLE","MILD","MODERATE","HIGH","SEVERE"},)

MATCH(CONCATENATE("""",N13,""""),{"NONE","NEGLIGIBLE","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

 

[Biff]

Hi!

Not sure what you're trying to do?

This function below works,
=MATCH("HIGH",{"NONE","NEGLIGIBLE","MILD","MODERATE","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