Combine VLOOKUP and HLOOKUP's capabilities

[DoooWhat]

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.

 

[DoooWhat]

Raw Data
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

Analysis
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

A better description of what I want in cell B2 (on the Analysis page)
is below. This hypothetical "INTERSECTION" formula would behave in
this manner:
INTERSECTION(lookup_value1,table_array1,lookup_value2,table_array2)

=INTERSECTION(B$1,'Raw Data'!$A:$A,$A2,'Raw Data'!$1:$1)

The result would give me the value in the cell that lies in the
intersection between these 2. I hope this clears up my intentions.
Thanks.

 

[Guest]

INDEX([array],MATCH(..),MATCH(...)) will do what you want, where the array is
the table in question, the first MATCH argument is the row you want to match
(account number in your case) and the second MATCH argument is the column you
want to match (date in your case). This returns the intersection (cell
contents) of the row/column match you specify.

Dave

 

[Guest]

Let me be more specific:
=INDEX(C26:H31,MATCH(D39,B26:B31,0),MATCH(D40,C25:H25,0))

This does what you're looking to do. The index array is C26:H31, the row
match is in B26:B31, based on the value in D39, and the column match is in
C25:H25, based on the value in D40.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

INDEX([array],MATCH(..),MATCH(...)) will do what you want, where the array is
the table in question, the first MATCH argument is the row you want to match
(account number in your case) and the second MATCH argument is the column you
want to match (date in your case). This returns the intersection (cell
contents) of the row/column match you specify.

Dave


--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

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.

 

[Guest]

In B2:

=INDEX($B$2:$Z$50,MATCH("Account id",$A$2:$A$50,0),MATCH("Date",$B$1:$Z$1,0))

Copy across and down: Account ID cell should be of form B$2 and Date of form
$A2


where "Account ID" = Acct1 etc, Date=01/02/2007 etc ... replace with cell
addresses on your report sheet.

All other ranges refer to your "Raw Data" sheet.

HTH

 

[Guest]

See my response. Your INTERSECTION function is
INDEX([array],MATCH(...),MATCH(...))

Dave

 

[DoooWhat]

I guess I overlooked the capabilities of the INDEX and MATCH function
used in conjunction. That worked perfectly. Thanks so much for the
great help and quick response!

Kevin

 

[Dave Peterson]

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

 

[Guest]

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

 

[Dave Peterson]

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