Vlookup to return multiple columns across two sheets

[davidanaya]

Hello,

Attached you will find my spreadsheet with two sheets. I am trying to write a vlookup B14 on Sheet 2 to populate B14-G14, pulling from the first sheet. Is there any way to return multiple columns from a different sheet using vlookup? I am basing my lookup on column a from both sheets. Please help!!

[Pete_UK]

You can use this formula in B14 of the sheet named Table:

=IFERROR(VLOOKUP($A14,'Table 1'!$A:$H,COLUMNS($A:B),0),"")

That cell is formatted as Text, so you need to format it as General, then you can copy the formula across into C14:H14, and then copy that row of formulae down as required.

Hope this helps.

Pete

[davidanaya]

One last question for you! how do I store formulas on a third sheet in order to cut and paste them into the proper locations on the first two sheets via VBA? everytime I try to copy/paste the formula changes.

[Pete_UK]

Just ensure that they are in the same relative position on Sheet3. So, the formula that I gave you above can be copied into cell B14 of Sheet3, and when you copy it back it also goes into cell B14 of the Table sheet, so the cell references do not change.

Hope this helps.

Pete

P.S. 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.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete