VLOOKUP – return multiple values/columns for one row/match of data

vlookup

I'm trying to figure out how to pull multiple columns to my target sheet when using VLOOKUP. for example

I have a list of employees on sheet one, with 100 rows of data and want to pull details about the employee from another sheet.

From the second sheet I want multiple columns that are next to each other. (i.e. D,E,F, and G.)

I'm doing the VLOOKUP from sheet one in column 5

lookup_value is A2 on sheet 1

Table_array is sheet 2 "A thru F"

but I want to pull columns D,E,F, and G or col_index_num – 4-7.

I'm doing them one at a time and I know there has to be a better way to get this.

Thanks.

Best Answer

You can use VLOOKUP in an array function to do this if you really want to.

There are details in this blog post but here is the summary:

  1. Select the cells (cells equal to the number of columns that you wish to fetch) where you wish to populate the VLOOKUP results.

  2. Next, without clicking anywhere else type the formula: VLOOKUP("Florian",A:D,{1,2,3,4},FALSE) in the Formula bar. The third argument i.e. {1,2,3,4} specifies the columns that need to be fetched.

  3. After this simply hit the Ctrl + Shift + Enter keys. This will enclose the above formula in curly brackets and the cells that you had selected will show the fetched columns.

Related Question