"Floating" Index Match

[Microsoft Junkie]

Hello all! I have spent many hours attempting to solve this problem and hope one of the geniuses on this forum could please help! I think I am doing something simple wrong, and it might just take a quick lookover.

I have a monthly task where I need to take my employer's budget from their software and put in into a better formatted and usable excel file. I have gotten pretty close to completely automating the task with some simple formulas, and now I want to finish the job. Essentially, I need an index match that only searches in certain areas, but these areas move as items are added or removed from the budget. I have attached a mini sheet that shows a small sample of what I would receive from the software.

Book1
	A	B	C	D
3	GeneralFund	Budget	Current Year	Prior Year
4	4611	$40	$30	$35
5	4612	$45	$37	$39
6	4613	$100	$120	$110
7
8	WaterFund
9	4611	$25	$22	$24
10	4612	$70	$65	$66
11	4613	$80	$72	$71
12
13	SewerFund
14	4611	$10	$13	$11
15	4612	$15	$14	$17
16	4613	$300	$325	$275
17
18
19		22
Output

Cell Formulas
Range	Formula
B19	B19	=INDEX(INDIRECT("'Output'!"&ADDRESS(MATCH("WaterFund",Output!$A:$A,0),3,2)&":"&"C13"),MATCH(4611,INDIRECT("'Output'!"&ADDRESS(MATCH("WaterFund",Output!$A:$A,0),1,2)&":"&"A13"),0))

For example, if I wanted to pull the current year numbers for line item 4611 from the Water Fund section to put into my budget sheet, I have been using this formula:

=INDEX(INDIRECT("'Output'!"&ADDRESS(MATCH("GeneralFund",'Output'!$A:$A,0),3,2)&":"&"C13"),MATCH(4611,INDIRECT("'Output'!"&ADDRESS(MATCH("GeneralFund",'Output'!$A:$A,0),1,2)&":"&"A13"),0))

Unfortunately, I have to go through each month and change C13 and A13 depending on how many line items have changed (which can be quite a bit!). I want to change the "C13" and "A13" to change automatically depending how many lines down the next fund ("SewerFund") is. It seems so simple, but I have struggled so hard! Please help!

 

[Microsoft Junkie]

Hello all! I have spent many hours attempting to solve this problem and hope one of the geniuses on this forum could please help! I think I am doing something simple wrong, and it might just take a quick lookover.

I have a monthly task where I need to take my employer's budget from their software and put in into a better formatted and usable excel file. I have gotten pretty close to completely automating the task with some simple formulas, and now I want to finish the job. Essentially, I need an index match that only searches in certain areas, but these areas move as items are added or removed from the budget. I have attached a mini sheet that shows a small sample of what I would receive from the software.

Book1
	A	B	C	D
3	GeneralFund	Budget	Current Year	Prior Year
4	4611	$40	$30	$35
5	4612	$45	$37	$39
6	4613	$100	$120	$110
7
8	WaterFund
9	4611	$25	$22	$24
10	4612	$70	$65	$66
11	4613	$80	$72	$71
12
13	SewerFund
14	4611	$10	$13	$11
15	4612	$15	$14	$17
16	4613	$300	$325	$275
17
18
19		22
Output

Cell Formulas
Range	Formula
B19	B19	=INDEX(INDIRECT("'Output'!"&ADDRESS(MATCH("WaterFund",Output!$A:$A,0),3,2)&":"&"C13"),MATCH(4611,INDIRECT("'Output'!"&ADDRESS(MATCH("WaterFund",Output!$A:$A,0),1,2)&":"&"A13"),0))

For example, if I wanted to pull the current year numbers for line item 4611 from the Water Fund section to put into my budget sheet, I have been using this formula:

=INDEX(INDIRECT("'Output'!"&ADDRESS(MATCH("GeneralFund",'Output'!$A:$A,0),3,2)&":"&"C13"),MATCH(4611,INDIRECT("'Output'!"&ADDRESS(MATCH("GeneralFund",'Output'!$A:$A,0),1,2)&":"&"A13"),0))

Unfortunately, I have to go through each month and change C13 and A13 depending on how many line items have changed (which can be quite a bit!). I want to change the "C13" and "A13" to change automatically depending how many lines down the next fund ("SewerFund") is. It seems so simple, but I have struggled so hard! Please help!

Ah, I made a mistake on the formula I posted! Should be the same as it is in the minisheet:

=INDEX(INDIRECT("'Output'!"&ADDRESS(MATCH("WaterFund",'Output'!$A:$A,0),3,2)&":"&"C13"),MATCH(4611,INDIRECT("'Output'!"&ADDRESS(MATCH("WaterFund",'Output'!$A:$A,0),1,2)&":"&"A13"),0))

 

[Marcelo Branco]

Welcome to Mr Excel Forum

Maybe something like this

Pasta1
	A	B	C	D	E	F	G	H	I	J
1
2
3	GeneralFund	Budget	Current Year	Prior Year			Criteria			Result
4	4611	40	30	35			Item	Section	Column
5	4612	45	37	39			4611	WaterFund	Budget	25
6	4613	100	120	110			4611	WaterFund	Current Year	22
7							4611	WaterFund	Prior Year	24
8	WaterFund						4612	GeneralFund	Budget	45
9	4611	25	22	24			4612	GeneralFund	Current Year	37
10	4612	70	65	66			4612	GeneralFund	Prior Year	39
11	4613	80	72	71			4613	SewerFund	Budget	300
12							4613	SewerFund	Current Year	325
13	SewerFund						4613	SewerFund	Prior Year	275
14	4611	10	13	11
15	4612	15	14	17
16	4613	300	325	275
17
Plan7

Cell Formulas
Range	Formula
J5:J13	J5	=INDEX(INDEX(A:A,MATCH(H5,A:A,0)):D$1000,MATCH(G5,INDEX(A:A,MATCH(H5,A:A,0)):A$1000,0),MATCH(I5,$A$3:$D$3,0))

Hope this helps

M.

 

[Microsoft Junkie]

Welcome to Mr Excel Forum

Maybe something like this

Pasta1
	A	B	C	D	E	F	G	H	I	J
1
2
3	GeneralFund	Budget	Current Year	Prior Year			Criteria			Result
4	4611	40	30	35			Item	Section	Column
5	4612	45	37	39			4611	WaterFund	Budget	25
6	4613	100	120	110			4611	WaterFund	Current Year	22
7							4611	WaterFund	Prior Year	24
8	WaterFund						4612	GeneralFund	Budget	45
9	4611	25	22	24			4612	GeneralFund	Current Year	37
10	4612	70	65	66			4612	GeneralFund	Prior Year	39
11	4613	80	72	71			4613	SewerFund	Budget	300
12							4613	SewerFund	Current Year	325
13	SewerFund						4613	SewerFund	Prior Year	275
14	4611	10	13	11
15	4612	15	14	17
16	4613	300	325	275
17
Plan7

Cell Formulas
Range	Formula
J5:J13	J5	=INDEX(INDEX(A:A,MATCH(H5,A:A,0)):D$1000,MATCH(G5,INDEX(A:A,MATCH(H5,A:A,0)):A$1000,0),MATCH(I5,$A$3:$D$3,0))

Hope this helps

M.

Thank you for taking a crack at it! Unfortunately, the budget is thousands of items long, so I do not think it would be practical to rearrange it into a different format each time. The data also comes a bit messier than what is shown in the sample chart, I was just trying to convey a point. Thank you very much for your time!

 

[Marcelo Branco]

You are welcome.

How many rows of data do you have? The formula can be adapted.

If the layout you showed in post 1 actually represents your real case, that is, section names in column A followed by item numbers and column headings in A3:D3, the formula should work. If not, try showing us a sample of data that matches your case.

Simplifying the data sample too much makes it difficult for you to get help solving your problem.

M.

 

[Microsoft Junkie]

You are welcome.

How many rows of data do you have? The formula can be adapted.

If the layout you showed in post 1 actually represents your real case, that is, section names in column A followed by item numbers and column headings in A3:D3, the formula should work. If not, try showing us a sample of data that matches your case.

Simplifying the data sample too much makes it difficult for you to get help solving your problem.

M.

I have about 2600 rows of data. I will get a better sample when I go to work tomorrow and post it here. There are no column headings in the data I get, but those could be easily added manually each month. Again, thank you very much.

Part of the issue is that I am leaving my role within the next week and I am trying to make this a completely hands off process. The current formula has been fine because I built it and I know how to modify it each month. My coworkers do not and are uninterested in learning.

 

[Microsoft Junkie]

You are welcome.

How many rows of data do you have? The formula can be adapted.

If the layout you showed in post 1 actually represents your real case, that is, section names in column A followed by item numbers and column headings in A3:D3, the formula should work. If not, try showing us a sample of data that matches your case.

Simplifying the data sample too much makes it difficult for you to get help solving your problem.

M.

Hey Marcelo, I just wanted to update you. I modified an index match return the nth instance of the number I was looking for and just counted to find how many 4611's there are and which one related to general, water, and so on. It worked well enough. Again, thank you for your time.