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:

John	potato	1
Mary	cranberry	5
David	salsa	4
Stacy	potato	6
Greg	potato	2
Anna	salsa	3

<tbody>
</tbody>

Into this:

John
Potato	1
Mary
Cranberry	5
David
Salsa	4
Stacy
Potato	6
Greg
Potato	2
Anna
Salsa	3

<tbody>
</tbody>

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.