Vlookup Multiple Worksheets - Populate multiple columns at same time

Hi,

Is there a vlookup formula that can match and populate multiple columns instead of only by one column when data is across two worksheets? For example, I want to match and populate all columns at the same time in worksheet #1's columns titled, Enrollment, Bld. Capacity and Ward from worksheet #2 using school ID as the unique ID. I have 49 columns I need to match up…but before I copy and paste the vlookup formula into each column and switch the column index number in the formula 48 times, wanted to see if there is an easier way of doing this.

Thanks for any help!

Sean

For Excel Forum_Vlookup.JPG

You haven't posted the formula that you are using (or proposing to use), but instead of using a fixed column index in the VLOOKUP function, you can use the function COLUMNS($A:B) instead. This will initially return 2, but as the formula is copied across, then the reference to column B will change, and thus return 3 (for column C), then 4 (for column D), and so on.

If you wanted to return the columns in a different order (but with the same column headings), then you can use a MATCH function to derive the appropriate column.

Hope this helps.

Pete

Thanks Pete! I will try this. Here is the current formula I am using. =VLOOKUP(N2,schoolsupply17,1,FALSE)

Well, instead of the 1 as the third parameter of the VLOOKUP, you can change this to a function which returns 1 (but which increments as it is copied across), like this:

=VLOOKUP($N2,schoolsupply17,COLUMNS($A:A),FALSE)

Note that I have put a dollar sign before the first N, so that it doesn't change as the formula is copied across.

Hope this helps.

Pete

Pete you are a wonderful Man! Works perfect. Thank you! Sean

Glad to hear that it works, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete