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
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):
The second argument to the first (in the formula, it's matching the second occurrence of the search number)
MATCH is the difference:
Andi described the first. The second and third work by calculating a reference to the cell after the match (the
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:
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
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):
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
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
OFFSET version, just to "future proof" the spreadsheet a bit.