|First, a posting tip:|
Before posting any more example data in this fourm, please click on the blue line at the end of this post and read the instructions found via that link.
That said, I'm not following exactly what you want to do, so I'll address this one issue for now. If there's something else you need help with, come on back.
When I drag the formula, the number 4...does not change. I want it to change to 5, 6, etc as I drag it
If you want the 4 to increment, you have to use an argument that will increment automatically.
One way to do that is to use the ROW function. The ROW function does nothing more than return the number of the Row it is used in.
e.g. if B6 contains =ROW(), it will return 6
Well, 6 - 2 = 4, so if you put this in B6, you'll get 4, drag it down one Row, you'll get 5, etc.
=VLOOKUP($A6, 'Subk'!$A7:$F18, ROW()-2, FALSE)
will return a col_num argument that is always 2 less than the Row in which the formula is used.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.