I have a data table that looks like this:

1/1/06 1.2 35 7.5 70 8.5 10
1/2/06 1.2 35 9.2 27
1/3/06 1.2 35 5.4 15 10.1 40

This is a download from an external database, so the format is fixed. The
first column is the date (obviously!), the second column is a reference
number, the third column is the "data" associated with the reference number
in the 2nd column, the 4th column is another reference number (using the same
date), the 5th number is the data associated with the 4th column, etc.

What I want to do is have another worksheet pick up the dates and the
appropriate reference number and place the correct data in the correct place.
This second worksheet is a grid; i.e., the reference numbers are in a row at
the top while the dates are listed in a column at the left.

I do not want to use a macro since the person that will use this data is not
familiar with macros. He's pretty good with Excel in general, so I was
hoping to write a formula that would load up the data into the grid sheet. I
thought a "lookup" formula of some kind would be the ticket.

Any help?

Can you email me what you are trying to do? I might be able to help you
out....



"Steve" wrote:

I have a data table that looks like this:

1/1/06 1.2 35 7.5 70 8.5 10
1/2/06 1.2 35 9.2 27
1/3/06 1.2 35 5.4 15 10.1 40

This is a download from an external database, so the format is fixed. The
first column is the date (obviously!), the second column is a reference
number, the third column is the "data" associated with the reference number
in the 2nd column, the 4th column is another reference number (using the same
date), the 5th number is the data associated with the 4th column, etc.

What I want to do is have another worksheet pick up the dates and the
appropriate reference number and place the correct data in the correct place.
This second worksheet is a grid; i.e., the reference numbers are in a row at
the top while the dates are listed in a column at the left.

I do not want to use a macro since the person that will use this data is not
familiar with macros. He's pretty good with Excel in general, so I was
hoping to write a formula that would load up the data into the grid sheet. I
thought a "lookup" formula of some kind would be the ticket.

Any help?

Thanks for the offer of help, but I figured it out.

Basically I used a "double MATCH". The first identified the row; the second
identified the column in that same row. I used INDEX to find the
"coordinates" using MATCH to find the first row. To MATCH the second number,
I used OFFSET inside MATCH to create an array. Here is the formula:

=INDEX('Output'!$D:$U,MATCH($F65,'Output'!$D:$D,0) ,MATCH(J$5,(OFFSET('Output'!$C$1,MATCH($F65,'Outpu t'!$D:$D,0)-1,0,1,17)),0))

I also set it up so that data could be added as needed - thus the '$D:$D'
notation instead of a specific array.

Actually, I used an 'IF' with an 'ISNA' statement to leave the cell blank if
there were no matches - which in reality could happen.

Thanks again.

"Difficult1" wrote:

Can you email me what you are trying to do? I might be able to help you
out....



"Steve" wrote:

I have a data table that looks like this:

1/1/06 1.2 35 7.5 70 8.5 10
1/2/06 1.2 35 9.2 27
1/3/06 1.2 35 5.4 15 10.1 40

This is a download from an external database, so the format is fixed. The
first column is the date (obviously!), the second column is a reference
number, the third column is the "data" associated with the reference number
in the 2nd column, the 4th column is another reference number (using the same
date), the 5th number is the data associated with the 4th column, etc.

What I want to do is have another worksheet pick up the dates and the
appropriate reference number and place the correct data in the correct place.
This second worksheet is a grid; i.e., the reference numbers are in a row at
the top while the dates are listed in a column at the left.

I do not want to use a macro since the person that will use this data is not
familiar with macros. He's pretty good with Excel in general, so I was
hoping to write a formula that would load up the data into the grid sheet. I
thought a "lookup" formula of some kind would be the ticket.

Any help?