copying data from matching cloumn

K

kuansheng

Hi,

I have in column K through W, column of data . The column heading is in
row K3:W3. In cell Y3 is the heading of a new column that the user will
enter. What i need to do is to find the matching cloumn heading from
K3:W3 and copy all the data from that column to the new column. Anyone
can help me out. Any help is appriciated, thanks you.
 
M

Max

One way ..

Assume column data (below the header row 3) is in rows 4 to 10

Select Y4:Y10

Put in the formula bar,
array-enter (press CTRL+SHIFT+ENTER):
=IF(TRIM(Y3)="","",OFFSET($K$4:$K$10,,MATCH(TRIM(Y3),K3:W3,0)-1))

Y4:Y10 will return the column of data for the input in Y3

Adapt to suit ..
 
B

Biff

Hi!

K3:W3 are column headers

Data is in the range K4:W10

Y3 = matching column header

Enter this formula in Y4 and copy down as needed:

=INDEX(K$4:W$10,ROWS($1:1),MATCH(Y$3,K$3:W$3,0))

Biff
 
M

Max

Additionally, to facilitate the input in Y3, we could also create a data
validation droplist in Y3 which grabs the column headers in K3:W3.

Select Y3
Click Data > Validation
Allow: List
Source: =OFFSET($K$3,,,,13)
Click OK
 
M

Max

And if we're using the DV droplist in Y3 ..
we could remove the TRIM() around Y3
in the earlier multi-cell array formula,
viz just use in Y4:Y10:
=IF(Y3="","",OFFSET($K$4:$K$10,,MATCH(Y3,K3:W3,0)-1))

The TRIM was used earlier as a safeguard against any
inadvertent extraneous spaces being entered within the inputs in Y3
(for more robust matching)
 
M

Max

You're welcome, kuansheng !
I'm glad it worked for you

See also Biff's offering which would work equally well,
with added benefits of non-array & non-volatility <g>
 
Top