Index function ALMOST works!

Hi,

I'm stuck with an indexing problem. The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing.

I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.

I'll attach and see if anyone can help. Thanks in advance!

You're returning the physical ROW value in the array so you either need to adjust this by -2 to account for the fact that rows 1 & 2 don't exist in the INDEX range (ie physical row 3 = row 1 in the INDEX range) or change the INDEX to start from row 1 and not row 3... I pefer the latter.

Have you thought of using a Pivot Table ?

Your syntax on the Small(if) array needed a little tweaking:

=IF(ROWS($A$4:$A4)>COUNTIF('Original Data'!$A$1:$A$1000,$A$2),"",INDEX('Original Data'!$A$1:$A$1000,SMALL(IF('Original Data'!$A$1:$A$1000=$A$2,ROW($A$1:$A$1000),""),ROWS($A$4:$A4))))

The section in blue really needs to start at row1, and as such best to go ahead and start them all there. The first two rows will be skipped anyway, so including them is harmless.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]