How to return multiple columns in excel

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