How to drag a formula with referenced cell increment by only 1?

[omcsarayar]

Hi, i know the title makes no sense, i dont know how else to describe it, i hope the picture below can make it clear what i'm trying to do.
So, on cell J2 i have the formula =B2 to copy the name from the reference table on the left. When i copy the formula on J2 it automatically incremented by 3, so it returns the value on J4. Is there a way to copy the formula on J2 to J5 but only incremented by 1 from the reference table (which is =B3)?

Any help will be much appreciated,
thankyou guyss!

- O

 

[ChrisFoster]

Yes, you can achieve this by using a mixed cell reference in your formula. To do this, you can use the dollar sign ($) to fix the row number in your formula, like this:

=B$2

 

[Joe4]

Those images look a bit odd.
Are you doing this in Excel, Google Sheets, or some other program?

 

[jdellasala]

Like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	No	Name	Age	Sex	Email	Rep		No	1. 2. 3.	Name Age Rep	M	F	Email
2	1	Misael Halvorsen	38	Male	misael@halvorsen	William			1	Misael Halvorsen
3	2	Karen Cruz	50	Female	karen@cruz	Grace		1	2	38	M		misael@halvorsen
4	3	Cheyenne Sheppy	42	Male	cheyenne@sheppy	John			3	William
5	4	Sofia Buckley	27	Female	sofia@buckley	John			1	Karen Cruz
6	5	Kayli Hagen	24	Female	kayli@hagen	Lily		2	2	50		F	karen@cruz
7									3	Grace
8									1	Cheyenne Sheppy
9								3	2	42	M		cheyenne@sheppy
10									3	John
11									1	Sofia Buckley
12								4	2	27		F	sofia@buckley
13									3	John
14									1	Kayli Hagen
15								5	2	24		F	kayli@hagen
16									3	Lily
Sheet1

Cell Formulas
Range	Formula
K3,K15,K12,K9,K6	K3	=IF(XLOOKUP(H3,$A$2:$A$6,$D$2:$D$6)="Male","M","")
L3,L15,L12,L9,L6	L3	=IF(XLOOKUP(H3,$A$2:$A$6,$D$2:$D$6)="Female","F","")
M3,M15,M12,M9,M6	M3	=XLOOKUP(H3,$A$2:$A$6,$E$2:$E$6)
J2,J5,J8,J11,J14	J2	=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6)
J3,J6,J9,J12,J15	J3	=XLOOKUP(H3,$A$2:$A$6,$C$2:$C$6)
J4,J7,J10,J13,J16	J4	=XLOOKUP(H3,$A$2:$A$6,$F$2:$F$6)

Note that if you highlight the top 3 rows (2-4) of a the columns with data, the formulas can be dragged down to the end of the table (row 16):

 

[omcsarayar]

Those images look a bit odd.
Are you doing this in Excel, Google Sheets, or some other program?

Hi there Joe, thank you for responding.
I'm doing this on google spreadsheet. I'm sorry if it looks odd, I was worried it would be blurry so I cropped out the picture)

 

[omcsarayar]

Like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	No	Name	Age	Sex	Email	Rep		No	1. 2. 3.	Name Age Rep	M	F	Email
2	1	Misael Halvorsen	38	Male	misael@halvorsen	William			1	Misael Halvorsen
3	2	Karen Cruz	50	Female	karen@cruz	Grace		1	2	38	M		misael@halvorsen
4	3	Cheyenne Sheppy	42	Male	cheyenne@sheppy	John			3	William
5	4	Sofia Buckley	27	Female	sofia@buckley	John			1	Karen Cruz
6	5	Kayli Hagen	24	Female	kayli@hagen	Lily		2	2	50		F	karen@cruz
7									3	Grace
8									1	Cheyenne Sheppy
9								3	2	42	M		cheyenne@sheppy
10									3	John
11									1	Sofia Buckley
12								4	2	27		F	sofia@buckley
13									3	John
14									1	Kayli Hagen
15								5	2	24		F	kayli@hagen
16									3	Lily
Sheet1

Cell Formulas
Range	Formula
K3,K15,K12,K9,K6	K3	=IF(XLOOKUP(H3,$A$2:$A$6,$D$2:$D$6)="Male","M","")
L3,L15,L12,L9,L6	L3	=IF(XLOOKUP(H3,$A$2:$A$6,$D$2:$D$6)="Female","F","")
M3,M15,M12,M9,M6	M3	=XLOOKUP(H3,$A$2:$A$6,$E$2:$E$6)
J2,J5,J8,J11,J14	J2	=XLOOKUP(H3,$A$2:$A$6,$B$2:$B$6)
J3,J6,J9,J12,J15	J3	=XLOOKUP(H3,$A$2:$A$6,$C$2:$C$6)
J4,J7,J10,J13,J16	J4	=XLOOKUP(H3,$A$2:$A$6,$F$2:$F$6)

Note that if you highlight the top 3 rows (2-4) of a the columns with data, the formulas can be dragged down to the end of the table (row 16):
View attachment 89704

Hello jdellasala, Thank you for responding. I hope you're having a great day.
I tried the formula and It works! Thank you so much for your help)

Best,
- O

 

[Joe4]

I'm doing this on google spreadsheet. I'm sorry if it looks odd

When I said "odd", I meant that it did not look like an Excel sheet.

When posting a question about Google Sheets, please be sure to mention it in your question because while there are many similiarities between the two programs, there also are a lot of differences. And not all Excel solutions will work in Google Sheets. Mentioning it will help ensure that you get answers that are pertinent to the program you are trying to do this in.