"Floating" Index Match

Microsoft Junkie

New Member
Joined
Jul 7, 2021
Messages
5
Office Version
  1. 365
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
ABCD
3GeneralFundBudgetCurrent YearPrior Year
44611$40$30$35
54612$45$37$39
64613$100$120$110
7
8WaterFund
94611$25$22$24
104612$70$65$66
114613$80$72$71
12
13SewerFund
144611$10$13$11
154612$15$14$17
164613$300$325$275
17
18
1922
Output
Cell Formulas
RangeFormula
B19B19=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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Microsoft Junkie

New Member
Joined
Jul 7, 2021
Messages
5
Office Version
  1. 365
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
ABCD
3GeneralFundBudgetCurrent YearPrior Year
44611$40$30$35
54612$45$37$39
64613$100$120$110
7
8WaterFund
94611$25$22$24
104612$70$65$66
114613$80$72$71
12
13SewerFund
144611$10$13$11
154612$15$14$17
164613$300$325$275
17
18
1922
Output
Cell Formulas
RangeFormula
B19B19=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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Welcome to Mr Excel Forum

Maybe something like this
Pasta1
ABCDEFGHIJ
1
2
3GeneralFundBudgetCurrent YearPrior YearCriteriaResult
44611403035ItemSectionColumn
546124537394611WaterFundBudget25
646131001201104611WaterFundCurrent Year22
74611WaterFundPrior Year24
8WaterFund4612GeneralFundBudget45
946112522244612GeneralFundCurrent Year37
1046127065664612GeneralFundPrior Year39
1146138072714613SewerFundBudget300
124613SewerFundCurrent Year325
13SewerFund4613SewerFundPrior Year275
144611101311
154612151417
164613300325275
17
Plan7
Cell Formulas
RangeFormula
J5:J13J5=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

New Member
Joined
Jul 7, 2021
Messages
5
Office Version
  1. 365
Welcome to Mr Excel Forum

Maybe something like this
Pasta1
ABCDEFGHIJ
1
2
3GeneralFundBudgetCurrent YearPrior YearCriteriaResult
44611403035ItemSectionColumn
546124537394611WaterFundBudget25
646131001201104611WaterFundCurrent Year22
74611WaterFundPrior Year24
8WaterFund4612GeneralFundBudget45
946112522244612GeneralFundCurrent Year37
1046127065664612GeneralFundPrior Year39
1146138072714613SewerFundBudget300
124613SewerFundCurrent Year325
13SewerFund4613SewerFundPrior Year275
144611101311
154612151417
164613300325275
17
Plan7
Cell Formulas
RangeFormula
J5:J13J5=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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 7, 2021
Messages
5
Office Version
  1. 365
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

New Member
Joined
Jul 7, 2021
Messages
5
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,147,518
Messages
5,741,637
Members
423,675
Latest member
Dea21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top