First the formula is wrong: this is despite it being given to you earlier.

=IF(LEFT(C2,4)="ur01","XX.XX.251.188",IF(LEFT(C2,4 )="ur02","XX.XX.251.189",IF(LEFT(C2,4)="ur03","XX. XX.251.192",IF(LEFT(C2,4)="ur04","XX.XX.251.193"," "))))

Place this formula in the first cell, then simply drag the formula down -
click on cell and move cursor to bottom right of cell until cross appears:
hold down left mouse button and drag down column. The C2 references will
automatically update to C3 etc.

"Janis" wrote:

copy/paste special/formula doesn't copy the relative reference. It copies
the same formula in every cell in the column . I want c2, c3,c4,c5...
instead of c2 in every cell. Is this because it is in a function?

Also, can't I get it to be blank in the cell if there is no match? All I
can see is the formula in every cell. It would be nice if it was blank.

tia,


=IF(LEFT(C2,4)="ur01","XX.XX.251.188",IF(LEFT(C2,4 )="ur02","XX.XX.251.189",IF(LEFT(C2,4)="ur03","XX. XX.251.192",if(left(C2,4)="ur04","XX.XX.251.193","
"),""),""),"")