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.