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
Bookmarks