Okay,
I need help with pulling data when the Col_Index_Num is to the left of the
lookup value in the array. I tried the following, with a negative 7 thinking
it would do the opposite...drive 7 rows to the left but it did not work.

=VLOOKUP(A1,Sheet2!A:H,-7,FALSE)
In this formula, the lookup value would have been in column H.

Thanks