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.

up vote 2 down vote accepted

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
  • 1
    Well, feel free to post your version too - yours may be easier to understand :) – Andi Mohr Oct 27 '15 at 18:05
  • 2
    @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
  • 1
    @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
  • 1
    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 INDIRECTs. 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.