Can I drag a set of 2 rows with formulas that reference the same row of another table

[pcvaccaro]

Hi smarties

I am trying to make a table that turns this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John
[/TD]
[TD]potato[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]cranberry[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]salsa[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Stacy[/TD]
[TD]potato[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]potato[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]salsa[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


Into this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Potato[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Cranberry[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Salsa[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Stacy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Potato[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Potato[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Salsa[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


The first table is a long list of rows that will continually grow. The second table is on another sheet. I want to be able to write the formulas in rows 1 and 2 (John & potato) and drag the set down the rest of the sheet. So far everything I've tried produces a result that skips rows from the first table. Instead of going John, Mary, David, Stacy it goes John, David, Greg.

I tried a few things with INDIRECT and OFFSET but I still get a result that skips rows from table 1.

Any ideas?

Thanks!

 

[DRSteele]

I have one idea. I think this works on your exemplar.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	B	C	D
3	Name	Food	Score
4	John	potato	1
5	Mary	cranberry	5
6	David	salsa	4
7	Stacy	potato	6
8	Greg	potato	2
9	Anna	salsa	3
10
11
12
13	Name and Food	Score
14	John
15	potato	1
16	Mary
17	cranberry	5
18	David
19	salsa	4
20	Stacy
21	potato	6
22	Greg
23	potato	2
24	Anna
25	salsa	3
Sheet29

Cell Formulas
Range	Formula
B14	=OFFSET($B$4,INT((ROW()-ROW($B$14)-2)/2)+1,MOD(ROWS($B$14:B14)-1,2))
C14	=IFNA(INDEX($D$4:$D$9,MATCH(B13&B14,$B$4:$B$9&$C$4:$C$9,0)),"")

_{^{<strike>
</strike>}}[/FONT]

 

[pcvaccaro]

Thanks! I'm going to give this a shot now.