# List from index function returning next row

1. ## List from index function returning next row

I have the following table in my Pasted Data sheet in cells B1:C17

Fund TransNo
3 300718
4 300718
2 100030472
4 100030472
4 100030476
8 100030476
4 100030482
6 100030482
3 100030601
4 100030601
2 100030602
4 100030602
4 100030605
8 100030605
4 100030606
6 100030606

On a second sheet, I'm using the following formula in D2 to find a list of all of the transactions that have a 6 in them. In the formula it's referencing D1 which contains a 6.

=IFERROR(INDEX('Pasted Data'!\$B\$2:\$C\$11219,SMALL(IF('Pasted Data'!\$B\$2:\$B\$11219=D\$1,ROW('Pasted Data'!\$B\$2:\$B\$11219)),ROW(1:1)),2),"")

I copy the above formula down and it correctly gives me two values, because there are two 6 values in the data. However, it's giving me the value in the next row, instead of the value next to the 6. So the results I'm getting are 100030601 and 0

I attached a copy of my excel sheet. The problem exists in all the formulas in the Transactions sheet, but you can't see it because sometimes the value of the next row down matches what the result should have been.

Thanks

2. ## Re: List from index function returning next row

change the B2 to B1 in the formula

Kind regards
Leo

3. ## Re: List from index function returning next row

Index the entire column:

=IFERROR(INDEX('Pasted Data'!\$C:\$C,SMALL(IF('Pasted Data'!\$B\$2:\$B\$11219=D\$1,ROW('Pasted Data'!\$B\$2:\$B\$11219)),ROWS(A\$1:A1))),"")

Also, replace ROW(1:1) with ROWS(A\$1:A1)

4. ## Re: List from index function returning next row

The Phil, Good evening.

To use an FILTER at your PASTED DATA is not an option?

It's very fast and you can use a SUBTOTAL function to sumarize it.

5. ## Re: List from index function returning next row

Great, thank you guys!!! This works now.

6. ## Re: List from index function returning next row

You're welcome. We appreciate the feedback!

7. ## Re: List from index function returning next row

Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1