1

I want to do the following: I have two sheets with the following data. And, I want to bring over columns 3 and 4 from Sheet2 over to Sheet1. The vlookup function will only allow me to bring over 1 column of data at a time. Is there a way to bring both columns at once?

SHEET 1 

Student     ISS OSS Total
John Doe    2       2
Mark Doe    2   1   3
Steven Doe  2   1   3
Kevin Doe       2   2

SHEET 2 

Name        Grade  Admin        Counselor
John Doe    9      McPherson    Gildea
Mark Doe    9      McPherson    Gildea
Steven Doe  9      Bagoly       Trudell
Kevin Doe   11     McPherson    Gildea
Improve this question
2
  • If you're OK combining Admin and Counselor into a single column, then that will work. Combine both lookup formulas in a cell with a separator like a comma in between them. However, there's no way in Excel to input a formula into a cell and have it directly modify the contents of another cell. In other words, you can't use one formula to pull John Doe's Admin into E2 and also their Counselor into F2.
    – Engineer Toast
    Commented Dec 19, 2016 at 18:08
  • Ron Rosenfeld's answer shows that what you asked can sort of be done, but I'm curious about the need to do it that way. You could stick a simple VLOOKUP for Admin in one cell, copy it to the adjacent cell for Counselor, then copy or drag the two formula's down the rows as needed. That's a simple formula that you can populate easily. What is the benefit of doing it the way you described?
    – fixer1234
    Commented Dec 20, 2016 at 3:05

1 Answer 1

Reset to default
1

You can do this by entering the column argument in VLOOKUP as an array constant. For example:

Sheet1!E2:   =VLOOKUP(A2,Sheet2!$A$2:$D$5,{3,4},FALSE)

Enter this in E2. Then Select E2:F2 and confirm by holding down ctrl + shift while hitting Enter.

The formula returns the array: {"McPherson","Gildea"} and entering it exactly the way I described returns the results into the two different cells.

You can then select E2:F2 and fill down as far as necessary.

Look at Excel HELP for information on Array Formulas and Array Constants.

Note that if you do this correctly, in the formula bar the formulas will appear the same in both E2 and F2; and also there will be braces {...} around the entire formula. Although you enter the braces when you type in the array constant {3,4} within the formula, Excel will add the braces that appear around the entire formula, when you enter a formula with the ctrl+shift+enter key combination.

enter image description here

Improve this answer
4
  • +1 This is clever, but you're really just pasting a formula into two cells at once. Is there a benefit or general use case (other than answering this question), to do this vs. using a simple VLOOKUP plus copying it to the adjacent cell? I'm intrigued by your solution, but I'm trying to figure out if there would ever be a practical use for it. :-)
    – fixer1234
    Commented Dec 20, 2016 at 3:20
  • 1
    @fixer1234 Have to test, but if the array constant can be a cell reference containing the array, and if you wanted to select different groups of columns to return and if your IT Dept did not allow VBA, then maybe. But I'm reaching.
    – Ron Rosenfeld
    Commented Dec 20, 2016 at 3:31
  • The formula worked. However, I realized I had to 1st select both cells E2:F2 when entering the formula and then hitting the ctrl+shift+enter key. Thanks to all who responded to my question.
    – Pebbles
    Commented Dec 20, 2016 at 16:28
  • @Pebbles Glad to help. I would appreciate it if you could accept my response as the Answer.
    – Ron Rosenfeld
    Commented Dec 21, 2016 at 11:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .