Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

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
2 Solutions
barry houdiniCommented:
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
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.
Rob HensonIT & Database AssistantCommented:
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.

Rob H
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Rob HensonIT & Database AssistantCommented:
Add a helper column to the left of the data with, for row 2:


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.

Rob H
Rob HensonIT & Database AssistantCommented:
See attached.
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.
The INDEXed range should include column S

INDEX('Cash Flow'!$G$2:$S$401,...
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:


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now