Excel Index and Match to capture data

I have a worksheet called Cash Flow (see attached sample) which is being generated from an accounting software. In the Financial_Data Worksheet I want to basically name in column A the items I want to chart and populate the numbers each month based on the Cash Flow worksheet. The idea is to generate a new Cash Flow report each month and the Financial_Data will then capture the new data which will then update a series of charts.

I have an Index and Match formula to capture the numbers I want but cannot get it to work
Test.xlsx
joeserroneAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
See attached.
Test.xlsx
0
 
barry houdiniConnect With a Mentor Commented:
You have to make sure all descriptions are in the same column of Cash Flow - I moved all to column B and then used this formula in the other sheet

=INDEX('Cash Flow'!$G$2:$R$401,MATCH($A2,'Cash Flow'!$B$2:$B$401,0),MATCH(B$1,'Cash Flow'!$G$1:$R$1,0))

see attached

regards, barry
Test-barry.xlsx
0
 
joeserroneAuthor Commented:
Hi Barry,
That is the problem, the accounting software will format that report in a particular way, is there anyway that search this from column A to E ? this is just an example, the actual report is much larger and this can become a convoluted process.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
Your initial formula also had a circular reference in the first section. The formula for retrieving data was within the area that you were looking to retrieve data from.

If you know (and can't change that) the headers are going to be in different columns, you can allow for this in the 3 different data retrieval formulae.

Net Income =INDEX('Cash Flow'!$D$1:$R$28,MATCH($A$2,'Cash Flow'!$D$1:$D$401,0),MATCH(B$1,'Cash Flow'!$D$1:$R$1,0))

Start Cash =INDEX('Cash Flow'!$B$1:$R$28,MATCH($A$3,'Cash Flow'!$B$1:$B$401,0),MATCH(B$1,'Cash Flow'!$B$1:$R$1,0))

End Cash =INDEX('Cash Flow'!$A$1:$R$28,MATCH($A$4,'Cash Flow'!$A$1:$A$401,0),MATCH(B$1,'Cash Flow'!$A$1:$R$1,0))

I have made bold the differences between each of the above.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
Add a helper column to the left of the data with, for row 2:

=B2&C2&D2&E2&F2

This will concatenate all headers into column A, assuming there isn't multiple headers on one row!!

Then hide and refer to column A for all as suggested by Barry. I would prefer the consistent formula approach rather than having to tweak depending on data required.

Thanks,
Rob H
0
 
joeserroneAuthor Commented:
That is a great Idea Rob, I added the helper column to concatenate and modified Barry's initial Formula to accommodate for the added column but the last index column is giving me a REF error.
Test.xlsx
0
 
NBVCCommented:
The INDEXed range should include column S


INDEX('Cash Flow'!$G$2:$S$401,...
0
 
joeserroneAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for joeserrone's comment #a38763747
Assisted answer: 250 points for barryhoudini's comment #a38763591
Assisted answer: 250 points for robhenson's comment #a38763678

for the following reason:

Thanks!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.