You're welcome--I'm sure you meant to thank Debra for her minor contribution
<vbg.

Marcello wrote:

Thanks to Dave, info helped me greatly in my spreadsheet.

"Dave Peterson" wrote:

Debra Dalgleish shows how to use =index(match()) for this:
http://www.contextures.com/xlFunctions03.html

DoooWhat wrote:

I have a set of data for which VLOOKUP and HLOOKUP alone will not be
dynamic enough. The MATCH and INDEX function does not get me where I
am going either.

This is a diagram of my raw data set

A B C D
1 Jan 1 Jan 2 Jan 3
2 Acct 1 balance balance balance
3 Acct 2 balance balance balance
4 Acct 3 balance balance balance

The dates will extend very far out to the right as I will add new
information on a weekly basis. There will also be a very large number
of accounts that may get reordered.

I need a formula that will return the balance on a specified day for a
specified account. The reason I need this is that while the raw data
page will be arranged as shown above, my analysis page (the one I will
be pulling the data to) will not be arranged in the same fashion. It
will look like this:

A B C D
1 Acct 1 Acct 2 Acct 3
2 Jan 1 balance balance balance
3 Jan 2 balance balance balance
4 Jan 3 balance balance balance

I would like the formula in cell B2, for instance, to do this: (In
referencing the raw data page) find the cell where Acct 1 and Jan 1
intersect and return the balance to the analysis page.

This analysis sheet will contain possibly more dates and accounts than
the raw data sheet, so there will not be a consistent number of rows/
columns between data when comparing the sheets. This is what blows
the lookups and match out of the water.

VLOOKUP, HLOOKUP, and MATCH require you to keep a rigidly static raw
data structure. For example, if I was to use VLOOKUP and tell the
formula to look over X amount of rows, this would all be fine until I
deleted a bunch of dates in between the reference cell and the data I
want returned.

Thanks for you help.

--

Dave Peterson


--

Dave Peterson