How to Use VLOOKUP to Return Multiple Columns in Excel

VLOOKUP is a powerful function in Excel that allows you to look up a value in a table and return data from another column in the same row. However, VLOOKUP can only return a single column at a time. To return multiple columns using VLOOKUP, you'll need to use a combination of VLOOKUP and other functions such as IFERROR, INDEX, and COLUMNS. Here's how you can use VLOOKUP to return multiple columns in Excel:

1. Ensure your data is organized in a table format with column headers.
2. Identify the lookup value, the column you want to look up the value in, and the columns you want to return.
3. In a separate cell or column, use the VLOOKUP function to return the data from the first column you want to retrieve.
4. To return additional columns, use the INDEX function combined with the VLOOKUP function. The INDEX function can return multiple columns from a range, while VLOOKUP can provide the row number for the lookup value.
5. Use the COLUMNS function to ensure that your formula works dynamically when you copy it to other cells.
6. Wrap your formula with the IFERROR function to handle errors when the lookup value is not found in the table.

Example

Let's say you have the following data in columns A to D:

A         B         C         D
-------------------------------------
Product   Price     Stock     Category
Apple     $1.00     50        Fruit
Banana    $0.50     100       Fruit
Carrot    $0.20     150       Vegetable
Donut     $1.20     10        Pastry

You want to look up the product "Banana" and return both the Price and Stock information in columns F and G.

1. Use VLOOKUP to return the first column (Price) in cell F2:

=VLOOKUP("Banana",A1:D4,2,FALSE)

2. Use INDEX and VLOOKUP to return the second column (Stock) in cell G2:

=INDEX(A1:D4,VLOOKUP("Banana",A1:D4,1,FALSE),3)

3. Make the formula dynamic by replacing the column number 3 with the COLUMNS function:

=INDEX(A1:D4,VLOOKUP("Banana",A1:D4,1,FALSE),COLUMNS($A1:C1))

4. Wrap the formula with IFERROR to handle errors:

=IFERROR(INDEX(A1:D4,VLOOKUP("Banana",A1:D4,1,FALSE),COLUMNS($A1:C1)),"")

5. Copy the formula in G2 to other cells if needed. The formula will now return multiple columns from the table using VLOOKUP.