2

Something I've wanted to do quite a bit lately, and can't work out how to do, is MATCH in a column I pass as an argument. Essentially, I have a two dimensional array, and I want to be able to find the first occurrence of a given value in the nth column, for any given value of n, and return the row number it occurs at. Alternatively (and more-or-less equivalently), I want to be able to search in the column with a given column header. Is there any way to do this?

Effectively, I want to simulate the non-existent function =MATCH(lookup_value,lookup_array,lookup_column,[match_type])

I've kludged together a horrible bodge job using INDIRECT, which works, but offends me horribly. =MATCH(lookup_value,INDIRECT("R"&<top of array>&"C"&<left of array>+<column reference>&":R"&<bottom of array>&"C"&<left of array>+<column reference>,FALSE),FALSE)

Tam Coton
  • 786
  • 1
  • 9
  • 20

5 Answers5

4

This formula should work for you and will avoid INDIRECT. Anytime you can avoid using Indirect, I recommend doing so.

=MATCH(lookup_value,INDEX(lookup_array,0,MATCH(lookup_header,array_headers,0)),0)

If you aren't looking up the column by column header and just have the column number, then it becomes easier:

=MATCH(lookup_value,INDEX(lookup_array,0,column_number),0)

tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • This is exactly what I was looking for - I had no idea you could put 0 in the row section of INDEX and have it work like that! Thank you! – Tam Coton Feb 11 '15 at 10:53
  • Can you also do `INDEX(lookup_array,row_number,0)` to return an entire row? – Tam Coton Feb 11 '15 at 15:58
0

You could do something like this:

Set findCell = ActiveSheet.Range("A:Z").Find(What:="term_to_search")

Will select a header based on your search term.

Set range = ActiveSheet.Range(findCell, findCell.Offset(DEF_MAX_ROWS, 0))

Set up a range which will search from that header down a whole column.

Matt
  • 500
  • 7
  • 18
0

You can specify a range in a formula using the INDIRECT function. So, for example, if you put the letter designation for the column you want to search in cell A75, you could use:

=MATCH("Value_To_Match", INDIRECT(A75 & ":" & A75), 0)

So, if the value in A75 is G, the string built up in the INDIRECT call is G:G, and the MATCH will look through column G for "Value_To_Match" and return the row number in which it's found.

Using this idea, you can put a formula into A75 that generates the column designation for the column you want to search. For example, if your column headers are all in row 1, and the header you want to search for is in A74, you can do:

=CHAR(MATCH(A74, 1:1, 0) + 64)

using the CHAR function to convert numbers into ASCII characters, so 65 becomes A, 66 becomes B, etc. Note that this will only work if you don't have columns past Z. You'd need a more fussy formula to do the right thing with AA, etc.

You can overcome the annoyances of dealing with column letters by using R1C1 notation instead, which you can activate by adding a second parameter of FALSE to the INDIRECT expression. Now, instead of specifying your column by a letter, you'll specify it using a number. This simplifies the column-finder in A75:

=MATCH(A74, 1:1, 0)

and also the INDIRECT expression in your overall MATCH:

=MATCH("Value_To_Match", INDIRECT("C" & A75, FALSE), 0)
Isaac Moses
  • 1,589
  • 6
  • 26
  • 44
  • @pnuts, for that to work, you have to change your settings to use R1C1 in general, or Excel will thing you're specifying a single cell in Column C. – Isaac Moses Feb 10 '15 at 15:50
  • @pnuts If A2 contains the value 5, that INDIRECT will return a reference to Cell C5, not to Column 5, unless you [change your settings](http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-i-change-the-r1c1-reference-style-to/5dd4aecc-f83b-476e-ac38-6769139502a0). – Isaac Moses Feb 10 '15 at 15:55
  • @pnuts, so, if you want to reference Cell C5 using A1 notation, how do you do it? – Isaac Moses Feb 10 '15 at 15:58
  • 1
    INDIRECT has an optional second argument. By default, it uses A1 notation, but it can be forced to use R1C1 notation. `INDIRECT("C5")` will reference cell C5, `INDIRECT("C5",0)` will reference column 5. – Tam Coton Feb 11 '15 at 11:01
  • @TamCoton Thanks for pointing that out. I've incorporated that into my answer. – Isaac Moses Feb 11 '15 at 14:56
0

For column references beyond Z you might switch notation (Excel Options, Formulas, Working with formulas and check R1C1 reference style) and, assuming the value to be looked up is in 'A1' (R1C1) with the column number in 'A2' (R2C1) apply: 

  =MATCH(R1C1,INDIRECT("C"&R2C1,0),0)

to save some complexity in converting a string of two or three characters into the relevant column number.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139
0

Say we have a two dimensional array: B3:E17 and we wish to locate Happiness in the third column of that array.

In G1 enter:

B3:E17

In G2 enter:

3

In G3 enter:

=ADDRESS(ROW(INDIRECT(G1)),COLUMN(INDIRECT(G1))+$G$2-1) & ":" & ADDRESS(ROW(INDIRECT(G1))+ROWS(INDIRECT(G1))-1,COLUMN(INDIRECT(G1))+$G$2-1)

This will display the address of that third column. Then in G4 enter:

=MATCH("Happiness",INDIRECT(G3),0)

For example:

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99