Double Lookup (V or H)?

[Noah114]

Is their a formula that can be creat in excel that lookups up to variables. I want to create a financial report that would have the following fields;

Payer January February March
ACS $10 $12 $14

What I would like is for each's month cell to have a lookup that looks up the Payer, then the date, and pulls the information from something that looks like this;

Payer Month Sales
ACS January $10
ACS February $12

I do not wish to create a pivot table each time, but simply dump the data, any help would be great!

 

[AnotherHiggins]

For future reference, you might consider running a speel chck () before posting, or just proof-reading*.

It was hard to read through to see what you were asking....

I'd turn on your macro recorder (Tools > Macros > Record new macro) and record a macro of creating a pivot table. If you need help editing the code, you can post in forum707, the VBA Visual Basic for Applications (Microsoft) Forum.

Do you have any imput on how this data is stored? I ask because the format you are looking for is how the data should be stored in the first place.

*I apologize if English is not your native language

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

You would need a new field that concatenates the Payer and the Month and look up that new single value to get your numbers

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Hi,

you could use INDEX combined with 2 MATCH functions to get a point in a matrix:

=INDEX($B$2:$M$13,MATCH(X1,$A$2:$A$13,false),MATCH(Y1,$B$1:$M$1,false))

assuming that your list of payers is in column X and your list of months in column Y.

Cheers,

Roel