One good soul has helped me get to this point, but I got one more contraint I'm trying to tackle:
How to populate Rep and Region based on the ID column, but this column may move, so getting this must be dynamic, just like getting Rep and Region columns is.
Here's the working formula, without the criteria:
=INDEX(VLOOKUP(ROW('From Sheet1'!A7:A); {ROW('From Sheet1'!A7:A)\ 'From Sheet1'!B7:C}; MATCH({"Rep"\ "Region"}; 'From Sheet1'!A7:C7; 0); 0))