# 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:

- Ensure your data is organized in a table format with column headers.
- Identify the lookup value, the column you want to look up the value in, and the columns you want to return.
- In a separate cell or column, use the VLOOKUP function to return the data from the first column you want to retrieve.
- 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.
- Use the COLUMNS function to ensure that your formula works dynamically when you copy it to other cells.
- 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.

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

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

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

`=INDEX(A1:D4,VLOOKUP("Banana",A1:D4,1,FALSE),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))`

- Wrap the formula with IFERROR to handle errors:

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

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

Did you find this useful?