VLOOKUP to Return Several Columns at Once
By ExcelReady TeamUpdated 6 min read
The short answer
To make VLOOKUP return multiple columns, lock the lookup cell and table with $ signs and let COLUMN supply the column number as you copy right: =VLOOKUP($F2,$A$2:$D$6,COLUMN(B1),FALSE). In Microsoft 365, =VLOOKUP(F2,A2:D6,{2,3,4},FALSE) spills all three columns at once. XLOOKUP can return several columns by selecting them as the return range.
Key takeaways
- One VLOOKUP returns one column, unless you give it several column numbers.
- Lock the lookup value's column ($F2) and the whole table ($A$2:$D$6) before copying across.
- COLUMN(B1) returns 2 and grows by one as you copy right, so it can be the column index number.
- In Microsoft 365 and Excel 2021, {2,3,4} as the column number spills three columns from one formula.
- XLOOKUP returns several columns when the return range is several columns wide.
On this page+
To make VLOOKUP return multiple columns for the same ID, such as name, department and salary, you either copy one formula across and let the column number change, or give VLOOKUP several column numbers at once. In Microsoft 365, XLOOKUP is even simpler: select every column you want as the return range. This guide shows each method on one employee table, with the $ signs that make copying work.
All the examples use this table in A1:D6, with the ID to find in F2 and results in G2:I2:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Name | Department | Salary |
| 2 | E-101 | Grace Kim | Finance | 58,000 |
| 3 | E-102 | Tomas Silva | Sales | 52,000 |
| 4 | E-103 | Aisha Bello | IT | 61,000 |
| 5 | E-104 | Marco Rossi | HR | 49,000 |
| 6 | E-105 | Yuki Tanaka | Sales | 55,000 |
Why one VLOOKUP returns only one column#
VLOOKUP's third argument, the column index number, picks a single column. So =VLOOKUP(F2,A2:D6,2,FALSE) returns Aisha Bello for E-103, and nothing else. If you copy that formula to the right, the 2 stays a 2, and every cell shows the name again. The column index number explained covers how that number is counted.
You have three ways around this: write a separate formula per column, make the column number change as you copy, or ask for several columns at once.
Method 1: copy across with COLUMN and $ signs#
This method works in every version of Excel. In G2, type:
=VLOOKUP($F2,$A$2:$D$6,COLUMN(B1),FALSE)Then select G2:I2 and fill right (Windows: Ctrl+R; Mac: ⌘R). You get:
| Cell | Formula's column part | Column number | Result |
|---|---|---|---|
| G2 | COLUMN(B1) | 2 | Aisha Bello |
| H2 | COLUMN(C1) | 3 | IT |
| I2 | COLUMN(D1) | 4 | 61,000 |
Three things make this work:
COLUMN(B1)returns the column number of B1, which is 2. As you copy right, it becomes C1 and D1, returning 3 and 4.$F2locks the column of the lookup cell, so every copy still looks at F. The row stays free, so you can also copy down for more IDs.$A$2:$D$6locks the whole table, so it doesn't slide right.
Because the row in $F2 is free, you can also fill the formula down. Put more IDs in F3, F4 and so on, select G2:I2, and fill down to the last ID. Every row returns its own employee's name, department and salary from one formula copied in two directions.
This is a classic use of mixed references. If the $ signs feel confusing, the absolute references lesson explains exactly when each one is needed. COLUMN counts from column A of the sheet, so this trick assumes the table starts in column A. If it starts elsewhere, use MATCH, below.
Method 2: match the header with MATCH#
If your result columns have the same headers as the table, MATCH can find each column number by name. Put Name, Department and Salary in G1:I1, then in G2:
=VLOOKUP($F2,$A$1:$D$6,MATCH(G$1,$A$1:$D$1,0),FALSE)Fill it right to I2. MATCH returns 2 for Name, 3 for Department and 4 for Salary. This version has two advantages: the results can be in any order, and it keeps working if someone inserts a column in the table. G$1 locks the row so the header reference stays in row 1 when you copy down.
Method 3: return several columns at once with an array#
In Microsoft 365 and Excel 2021, you can give VLOOKUP a list of column numbers inside curly braces:
=VLOOKUP(F2,A2:D6,{2,3,4},FALSE)Type it once in G2 and press Enter. The three results spill into G2, H2 and I2: Aisha Bello, IT and 61,000. The braces make an array constant, a fixed list of values typed into the formula. You can use any order, such as {4,2} for salary then name.
Keep the cells to the right empty, or Excel shows a #SPILL! error. In older versions you'd have to select all three cells first and press Ctrl+Shift+Enter to enter it as an array formula, which is why Method 1 is more common there.
Method 4: use XLOOKUP instead#
XLOOKUP returns several columns when the return range is several columns wide:
=XLOOKUP(F2,A2:A6,B2:D6)This spills Aisha Bello, IT and 61,000 into G2:I2. There are no column numbers to count, nothing breaks when you insert a column, and an exact match is the default. XLOOKUP needs Excel 2021 or Microsoft 365. The comparison of VLOOKUP vs XLOOKUP covers when to switch, and the XLOOKUP function guide has the full syntax.
Combine several columns into one cell#
Sometimes you want the results joined in one cell, such as "Aisha Bello (IT)". Join two lookups with &:
=VLOOKUP(F2,A2:D6,2,FALSE)&" ("&VLOOKUP(F2,A2:D6,3,FALSE)&")"The result is Aisha Bello (IT). Each VLOOKUP finds the same row and returns a different column.
Common mistakes when returning multiple columns#
- Forgetting FALSE. Without it, every column comes from an approximate match. The guide to VLOOKUP exact match explains why.
- Missing
$signs. An unlocked table slides right as you copy and returns #N/A or #REF!. - Locking the wrong part.
F$2locks the row instead of the column, so copying right moves the lookup to G2. - Using COLUMN when the table doesn't start in column A. The numbers will be off. Use MATCH instead.
- Blocked spill range. Clear the cells where the array or XLOOKUP results need to go.
If you still see errors, work through why VLOOKUP is not working. Refresh the basics in the VLOOKUP beginner's guide and the VLOOKUP function guide.
Practise it#
Copying lookups across columns is where $ signs finally make sense. Practise VLOOKUP in a live spreadsheet: you type the formula and it checks the result instantly. Then practise absolute and mixed references so your lookups copy across and down without breaking.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Return the department for employee F2. In H2, write the copy-across version of VLOOKUP: lock $F2 and $A$2:$D$6, and use COLUMN(C1) as the column number.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Salary | Department | |||||||
| 2 | E-103 | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 |
Tip: COLUMN(C1) returns 3, and Department is the third column of A2:D6.
Practise this in a lesson
Frequently asked questions
Can VLOOKUP return multiple columns?
Yes. In Microsoft 365 or Excel 2021, use an array of column numbers like {2,3,4}, and the results spill across. In any version, copy the formula right and use COLUMN to change the column number.
How do I copy a VLOOKUP across columns?
Lock the lookup cell's column and the table with $ signs, like $F2 and $A$2:$D$6, and replace the column number with COLUMN(B1). Then copy right; the column number goes up by one in each cell.
Why does my VLOOKUP return the same column when copied across?
The column index number is a typed number, so it doesn't change when you copy. Use COLUMN(B1) or MATCH on the header row instead.
How do I return a whole row with XLOOKUP?
Select several columns as the return range: =XLOOKUP(F2,A2:A6,B2:D6). The result spills across three cells. This needs Excel 2021 or Microsoft 365.