Match() worksheet function

[WCM]

Excel Help indicates that the Match() function works for an Array, but I can
only get it to work for a 'list' (i.e., does not work for an array with more
than one column - does not work for a two dimensional array).

Am I missing something? Thanks in advance for you help ...

Bill

 

[T. Valko]

MATCH...

The lookup_array must be a one dimensional array.

 

[WCM]

Then is there a best way to find location of a single literal value (or
referenced value) in a two-dimensional array?

Say I have alpha characters in the Excel range A1:M9. I want to find the
first location of "G" in that array. Is there any function that would return
say (5, 7) - i.e, for location of "G" in row-5, col-7?

 

[T. Valko]

It depends on what you consider the "first" location:

...........A..........B
1........X.........Y
2........Y.........X

What is the first location of Y ?

Is it B1 or A2 ?

 

[WCM]

That would depend on how excel does the search - whichever "Y" it runs into
first. For my purpose, any cell address with the value "Y" would work.
Specifically, what I need is the row number of any "Y" in the A1:M9 array -
doesn't matter which "Y". I will use that row number to get the value in
column Q for that row number.

Once I get the row number I know what to do to get the value in column Q for
that row.

My problem is getting a row number for the value "Y" when "Y" exists in a
range (not a single column). Bottom line, I need the equivolent of a
Match() function that works for a two-dimensional array.

Is there any Excel function (or combination of functions) that does that?

Thanks ...

 

[T. Valko]

If your range is A1:M9...

Array entered** :

=MIN(IF(A1:M9="G",ROW(A1:M9)))

This will return the (absolute) row number of the first location of "G" from
top to bottom.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[WCM]

That is perfect! Thank you ...

If your range is A1:M9...

Array entered** :

=MIN(IF(A1:M9="G",ROW(A1:M9)))

This will return the (absolute) row number of the first location of "G" from
top to bottom.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[WCM]

for posterity, here is final formula, and it's working as advertised ...
thanks again:

{=INDIRECT("S"&TEXT(MIN(IF($D$1:$P$5000=A1,ROW($D$1:$P$5000))),"0"))}

 

[T. Valko]

You'll get the same result without the TEXT function:

{=INDIRECT("S"&MIN(IF($D$1:$P$5000=A1,ROW($D$1:$P$5000))))}

 

[WCM]

Got it ... thanks again ... this exercise totally got me into array formulas
- a whole new world ...