Index/Match & Offset Problem

[VbaNoob1]

Hi, below I created a simplified version of two worksheets I have. I'm trying to create a formula in Sheet2 that will find the 1st instance of the fund# in Sheet1 B:B, get the value to the left of it in A:A, offset that cell by the amount of rows between that cell and the 1st instance of account# 300300300 and then offset that reference by 1 column to get the ending ledger. My formula right now is =OFFSET(OFFSET(Index(Sheet1!A:B,MATCH(Sheet2!A2,Sheet1!B:B,0),1),MATCH(300300300,Sheet1!A:A,0)-MATCH(INDEX(Sheet1!A:B,MATCH(Sheet2!A2,Sheet1!B:B,0),1),SHEET1!A:A,0),0),0,1)

The formula works for the Fund ABC i believe, but breaks for Fund XYZ because it uses the first instance of account# 300300300 from Fund ABC instead of from Fund XYZ.

Sheet1 is 10,000+ lines but a solution to this example would help greatly. Thank you!

Sheet1

	A	B
1	Fund ABC	800540
2	Account #	Ending Ledger
3	100100100	500.00
4
5	Fund ABC	800540
6	Account #	Ending Ledger
7	300300300	1000.00
8
9	Fund DEF	800640
10	Account #	Ending Ledger
11	100100100	400.00
12
13	Fund DEF	800640
14	Account #	Ending Ledger
15	300300300	900.00

<tbody>
</tbody>

Sheet2

	A	B
1	Fund #	Ending Ledger
2	800540
3	800640

<tbody>
</tbody>

 

[steve the fish]

Your example isnt clear. You mention ABC and XYZ. Where are these in the output table? How can you use them if they arent provided?

 

[jasonb75]

See if this helps

=INDEX(Sheet1!B:B,MATCH(300300300,INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0)):INDEX(Sheet1!A:A,ROWS(A:A)),0)+MATCH(A2,Sheet1!B:B,0)-1)