Formula to choose from the last column of a given row
 

I have a table that looks like this:

Item 7/9 7/10 7/11
A 23 342 2323
B 343 219 643

Every day I add a new column with some additional data. Sometimes I add a new row for a new item.

I would like a second table that will draw from the first table, and give me the values in the last two columns of the first table for each item. So it might look like this:

Item Col1 Col 2
A 342 2323
B 219 643

Is there a formula I can use that will always pull the last two columns' worth of data for me?

Thanks much,
David

Formula to choose from the last column of a given row
 

On Thu, 12 Jul 2012 16:10:46 -0700 (PDT), David Tannenbaum wrote:

I have a table that looks like this:

Item 7/9 7/10 7/11
A 23 342 2323
B 343 219 643

Every day I add a new column with some additional data. Sometimes I add a new row for a new item.

I would like a second table that will draw from the first table, and give me the values in the last two columns of the first table for each item. So it might look like this:

Item Col1 Col 2
A 342 2323
B 219 643

Is there a formula I can use that will always pull the last two columns' worth of data for me?

Thanks much,
David

IF your data table begins in A1; and if there are no blank cells in column A or row 1, then one way is to use a dynamic range name to define your data table:

eg: Define Name
Name: DataTbl
Scope: Workbook
Refers To: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Then, with your 2nd table starting in column M (adjust the references depending on where you have this new table)

To return col1 for Item A (where "A" is in M15):
=INDEX(DataTbl,MATCH($M15,$A:$A,0),COLUMNS(DataTbl )-1)

To return col2 for Item A:
=INDEX(DataTbl,MATCH($M15,$A:$A,0),COLUMNS(DataTbl ))

And with your Item names in col M; just select N15:O15 and fill down as far as required.

Formula to choose from the last column of a given row
 

Awesome! Thank you!

On Thursday, July 12, 2012 5:01:08 PM UTC-7, Ron Rosenfeld wrote:
On Thu, 12 Jul 2012 16:10:46 -0700 (PDT), David Tannenbaum wrote:



I have a table that looks like this:



Item 7/9 7/10 7/11

A 23 342 2323

B 343 219 643



Every day I add a new column with some additional data. Sometimes I add a new row for a new item.



I would like a second table that will draw from the first table, and give me the values in the last two columns of the first table for each item. So it might look like this:



Item Col1 Col 2

A 342 2323

B 219 643



Is there a formula I can use that will always pull the last two columns' worth of data for me?



Thanks much,

David



IF your data table begins in A1; and if there are no blank cells in column A or row 1, then one way is to use a dynamic range name to define your data table:



eg: Define Name

Name: DataTbl

Scope: Workbook

Refers To: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))



Then, with your 2nd table starting in column M (adjust the references depending on where you have this new table)



To return col1 for Item A (where "A" is in M15):

=INDEX(DataTbl,MATCH($M15,$A:$A,0),COLUMNS(DataTbl )-1)



To return col2 for Item A:

=INDEX(DataTbl,MATCH($M15,$A:$A,0),COLUMNS(DataTbl ))



And with your Item names in col M; just select N15:O15 and fill down as far as required.