|In your OP you said: "...and return the value that is always in column C" so that is what I solved.|
To pull values from Column F, just expand the array for the INDEX function and change the column_num argument to match the relative position of Column F within the array:
Note: The row_num and column_num arguments are relative positions within the array, not the actual Row or Column number that Excel uses.
In other words, Column F is in relative position 5 within the array of columns B:F. e.g. B is 1, C is 2, F is 5. The same is true for the MATCH function. It returns a relative position within the lookup_array, not an actual Excel Row or Column number.
If the array starts in Row 1 and/or Column A, then the relative position will be the same as the Row and/or Column number, but if the array starts anywhere else, the numbers won't match.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.