Excel Help - Is it possible to return the column number of the 2nd match within a horizontal array?

I'm trying to return the column number that corresponds to the 2nd occurrence of zero in the following table:

``````B       C       D       E       F      G
2       0       0       3       4      9
``````

For example, the desired formula will return a 3 representing column D.

If that is not possible to return the column number within the array then is it possible to return a 4 instead (column number within the worksheet)?

Thanks in advanced for your help.

2 Answers

I'm sure somebody will have a much neater way to do this, but here's a quick answer I've hacked together.

It requires you to specify the range (B1:G1 in your example) in some `MATCH` formulas, and also to specify that range again in R1C1 format for use in the `INDIRECT` function.

``````=MATCH(0,INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0),0)+MATCH(0,B1:G1,0)
``````

Walkthrough

We start by finding the column holding your first matched value, that's this bit of the formula: `MATCH(0,B1:G1,0)`.

Then once we know that the first match is in column C, we can run another `MATCH` that looks in the range to the right of Column C - ie Column D onwards. That's what the `INDIRECT` is doing. It uses R1C1 referencing so that it can take your first `MATCH` as an input: `INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0)`.

So then we run our second `MATCH` like this: `MATCH(0,INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0),0)`.

That will output the number of the column counting up from Column D (ie 1 in this case). So we need to add the number of columns we skipped on the left, to get our final number - this is just a repeat of the first MATCH: `+MATCH(0,B1:G1,0)`.

Note - For flexibility use INDEX() or OFFSET() rather than INDIRECT()

Blm raises excellent points about the inflexibility of using `INDIRECT` - read his answer proposing `INDEX` or `OFFSET` instead.

• Darn, you beat me to it, I was just about to post something similar, except I used OFFSET to calculate the range for the second match, although either should work just fine. – blm Oct 27 '15 at 18:04
• Well, feel free to post your version too - yours may be easier to understand :) – Andi Mohr Oct 27 '15 at 18:05
• @blm please post your version! Different optional ways to solve things may give new combined ways to solve the problem. – Hannu Oct 27 '15 at 20:02
• @Hannu Ok, posted. In addition to a couple of other (closely related, and closely related to the above) solutions, I actually found a flaw in the above formula, which I've described in my answer. – blm Oct 27 '15 at 21:42
• Thanks much. This was a good exercise, I use `INDIRECT` all over the place and am often faced with the "fragility" problem--I modify a sheet and then have to fix up all the `INDIRECT`s. I'll definitely be looking at modifying them to use `INDEX` or `OFFSET` instead. It's always nice when both the poster of the question and the poster of an answer learn something. :-) – blm Oct 27 '15 at 22:49

By popular demand... :-), plus there are a couple of problems with the `INDIRECT` version, which I describe below.

First, two other solutions. The only substantive differences are how the second range to search is calculated. Andi used `INDIRECT`, I use `INDEX` and `OFFSET` respectively. One other small difference you'll notice, instead of hardcoding the value to search for, I get it from a cell, which just makes it easier to test the formulas by varying the number being searched for. I've included all three here so it's easy to see them next to each other, the first is just copied from Andi's answer (with the hardcoded 0 replaced with the same cell reference I use in the others, B3):

``````=MATCH(B3,INDIRECT("R1C"&MATCH(B3,B1:G1,0)+1&":R1C7",0),0)+MATCH(B3,B1:G1,0)
=MATCH(B3,INDEX(B1:G1,0,MATCH(B3,B1:G1,0)+1):G1,0)+MATCH(B3,B1:G1,0)
=MATCH(B3,OFFSET(B1:G1,0,MATCH(B3,B1:G1,0)):G1,0)+MATCH(B3,B1:G1,0)
``````

The second argument to the first (in the formula, it's matching the second occurrence of the search number) `MATCH` is the difference:

``````INDIRECT("R1C"&MATCH(B3,B1:G1,0)+1&":R1C7",0)
INDEX(B1:G1,0,MATCH(B3,B1:G1,0)+1):G1
OFFSET(B1:G1,0,MATCH(B3,B1:G1,0)):G1
``````

Andi described the first. The second and third work by calculating a reference to the cell after the match (the `INDEX(...)` and `OFFSET(...)`), and then extending that to a range that ends with the last cell (the `:G1` part). So if searching for 0, `MATCH(B3,B1:G1,0)` returns 2, so the `INDEX` version becomes:

``````INDEX(B1:G1,0,2+1):G1
INDEX(B1:G1,0,3):G1
D1:G1
``````

The `OFFSET` version behaves similarly.

Although the three are on the face of it equivalent, there are actually two things that would make me not use the `INDIRECT` version. The first is that because it calculates an absolute column, it doesn't work in slightly different circumstances. Let's say the final value in the table is changed from a 9 to a 3. Then if we place 3 in B3 to search for the second 3, the formulas should all return 6. However, the `INDIRECT` version returns 5. Here's why. In that case, `MATCH(B3,B1:G1,0)` returns 4, so the `INDIRECT` becomes:

`````` INDIRECT("R1C"&4+1&":R1C7",0)
INDIRECT("R1C"&5&":R1C7",0)
INDIRECT("R1C5:R1C7",0)
``````

which is equivalent to E1:G1. However, the first cell of E1:G1 is the cell containing the first 3, so the `MATCH` will just find the first 3 again and return 1, which gets added to the first `MATCH` value (4), returning 5.

The issue is that the column calculation is absolute and doesn't take into account the fact that the table being searched starts in the second column. So even when searching for 0, the `INDIRECT` formula is "wrong", in that it's finding the first 0 twice, but it looks like it works because the second 0 is immediately to the first 0's right, so the `MATCH` looking for the second 0 returns 1, which happens to be the difference between the column numbers of the two 0's. When the difference between the column numbers of the two numbers being searched for isn't 1, then the `INDIRECT` formula will return the wrong value.

That's fixable by making the column reference relative (and assuming that the formula is in the same column as the start of the table):

``````=MATCH(B3,INDIRECT("R1C["&MATCH(B3,B1:G1,0)&"]:R1C7",0),0)+MATCH(B3,B1:G1,0)
``````

Now the `INDIRECT` is:

`````` INDIRECT("R1C["&4&"]:R1C7",0)
INDIRECT("R1C[4]:R1C7",0)
``````

which is F1:G1, so now the second `MATCH` will start searching in the cell to the right of the first match, and will return 2, resulting in a total of 6, which is correct.

That works, but there's still a problem, related to my parenthetical "assuming that the formula is in the same column as the start of the table". The `INDIRECT` version (even the fixed one) is fairly fragile. For example, if I insert a new row above the table of numbers, the `INDEX` and `OFFSET` versions continue to work, because Excel automatically updates all the references. But because all but one of the references in the `INDIRECT` version are text, Excel can't update them, so they'll continue to refer to the first row, which is now something else. If you remove the first column or add additional columns to the left of the table, it has similar problems. So in this case, I'd probably opt for the `INDEX` or `OFFSET` version, just to "future proof" the spreadsheet a bit.

• I agree with you - either `INDEX` or `OFFSET` are improvements on using `INDIRECT`. If the OP wants to marks yours as correct I'm in favour! – Andi Mohr Oct 27 '15 at 22:30
• I wouldn't agree that OFFSET is an "improvement" on INDIRECT. Both are volatile functions. If a set-up with INDEX can be achieved - which in my experience it almost always can in this type of scenario - then it should be favoured. – XOR LX Oct 28 '15 at 7:04
• I just meant an improvement, in this situation. – Andi Mohr Oct 28 '15 at 9:05