How can I use a formula throughout the column??

[Tig]

Hi,

I am using the INDEX and MATCH functions to match two tables that I have and return a value. I am wanting to apply the formula to all cells in the column but when I drag the formula down it changes all of the cell numbers not just the one variable. I have inputted the formula into 10 cells and dragged them together to see if it can detect my pattern but this does not work either.

My code

=INDEX(Z2:Z36,MATCH(A2,Y2:Y36,0))

I want the next to be =INDEX(Z2:Z36,MATCH(A3,Y2:Y36,0))

and for just the A number to change

BUT it seems to be changing it to

=INDEX(Z3:Z37,MATCH(A3,Y3:Y37,0))


Is there any way of doing this?

Thanks

 

[doofusboy]

Change to this before copying formula:

=INDEX($Z$2:$Z$36,MATCH(A2,$Y$2:$Y$36,0))

 

[Rick Rothstein]

You need to become familiar with relative and absolute referencing. Read the information on this webpage...

http://www.cpearson.com/excel/relative.aspx

 

[Expiry]

Dollar signs. =A1, =$A$1, =$A1, =A$1.

The dollar locks whichever part of the cell refs follows it.

 

[Tig]

Doofusboy, Expiry - thanks for this - this has worked a treat!

Rick - thank you - I will use this as a resource, I am fairly new to excel, I can do the basics but want to learn more.

Have you got any more resources that you would recommend?

Thanks