How to Use VLOOKUP in Excel for Multiple Columns

“VLOOKUP for multiple columns” usually means one of two things. To return several columns for one match, use an array of column numbers: =VLOOKUP(F2,$A$2:$D$100,{2,3,4},FALSE) returns columns 2, 3 and 4 side by side in Microsoft 365. In older versions, copy the formula across with COLUMN() as the index. To match on more than one column, such as first and last name, add a helper column that joins them, then look up the joined value. In Microsoft 365 and Excel 2021, XLOOKUP does both more simply. Here’s each technique with examples.

A quick VLOOKUP refresher

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE) looks for a value in the first column of a table and returns a value from a column to the right. FALSE means exact match. If you’re new to VLOOKUP, start with how to do a VLOOKUP in Excel.

In the examples below, A2:D100 holds Product ID, Name, Price and Stock, and F2 holds the ID you’re looking up.

Part 1: Return several columns at once

Microsoft 365: use an array of column numbers

=VLOOKUP(F2,$A$2:$D$100,{2,3,4},FALSE)

The result spills into three cells: Name, Price and Stock. Keep the two cells to the right empty, or you’ll get #SPILL!. You can pick any columns in any order, for example {4,2} for Stock, then Name.

Any version: copy the formula across with COLUMN

1. In G2, type =VLOOKUP($F2,$A$2:$D$100,COLUMN(B1),FALSE).
2. Drag it right to H2 and I2.

COLUMN(B1) returns 2, and it becomes C1 (3) and D1 (4) as you copy right, so each cell returns the next column. The dollar signs keep F2 and the table fixed. See how to keep one cell constant in Excel.

Match the headers instead of counting columns

If the column order might change, find each column by its heading. With headings in A1:D1 and the heading you want in G1:

=VLOOKUP($F2,$A$2:$D$100,MATCH(G$1,$A$1:$D$1,0),FALSE)

Now the formula keeps working even if someone inserts or rearranges columns.

Part 2: Match on more than one column

VLOOKUP can only search one column, so the trick is to make one column that combines the criteria.

Helper column method

1. Insert a new column A before your data, so the helper is the first column of the lookup table.
2. In A2, combine the criteria, for example first name in B and last name in C: =B2&"|"&C2. Copy it down. The | separator prevents false matches, such as “Ann Lee” matching “Anne Ee”.
3. Look up the combined value: =VLOOKUP(H2&"|"&I2,$A$2:$E$100,4,FALSE), where H2 and I2 hold the first and last name you’re searching for.

You can hide the helper column afterwards. For more ways to join columns, see how to combine columns in Excel.

Without a helper column (Microsoft 365)

=XLOOKUP(1,($B$2:$B$100=H2)*($C$2:$C$100=I2),$D$2:$D$100,"Not found")

Each condition returns TRUE or FALSE, and multiplying them gives 1 only where both match.

Part 3: Return every matching row

VLOOKUP and XLOOKUP stop at the first match. If an ID or name appears several times, such as a customer with many orders, and you want all the rows, use FILTER in Microsoft 365:

=FILTER($B$2:$D$100,$A$2:$A$100=F2,"No matches")

It returns every row where column A equals F2, with all three columns, and the results spill down and across. Add more conditions by multiplying them: =FILTER($B$2:$D$100,($A$2:$A$100=F2)*($C$2:$C$100>50)).

Worked example: an order form

Say Sheet2 holds a product list (ID, Name, Price, Stock) and Sheet1 is an order form where staff type an ID in A2:

1. In B2, enter =IFERROR(VLOOKUP($A2,Sheet2!$A$2:$D$500,COLUMN(B1),FALSE),"").
2. Copy it right to D2, then copy B2:D2 down the form.
3. Type an ID in any row of column A, and the name, price and stock fill in automatically.
4. Add a total column with =IF(C2="","",C2*E2), where E holds the quantity.

The IFERROR wrapper keeps empty rows blank instead of showing #N/A, which makes the form tidier to print.

The easier option: XLOOKUP

In Microsoft 365, Excel 2021 and later, XLOOKUP handles multiple columns directly:

• Return several columns: =XLOOKUP(F2,$A$2:$A$100,$B$2:$D$100)
• Look to the left, which VLOOKUP can’t do.
• Add a “not found” message without IFERROR.

See how to do an XLOOKUP in Excel. Stick with VLOOKUP if people will open the file in Excel 2019 or older.

Look up across sheets

Every formula above works with a table on another sheet. Just include the sheet name, such as Products!$A$2:$D$100. See how to do a VLOOKUP between two sheets and how to match data from two worksheets.

Troubleshooting

#N/A

No match was found. Check for extra spaces (use TRIM), numbers stored as text, or a helper column whose separator differs from the one in your lookup value. Wrap the formula in IFERROR(...,"Not found") to show a friendly message.

#REF!

The column number is bigger than the number of columns in the table. A table of A:D has only 4 columns.

Wrong values when copying across

Check the dollar signs: $F2 and $A$2:$D$100 must stay fixed.

#SPILL!

Cells to the right of the array formula aren’t empty. Clear them.

Related guides

• How to use XLOOKUP with two sheets
• How to compare two sheets in Excel for matches

Summary

1. Several results: =VLOOKUP(F2,$A$2:$D$100,{2,3,4},FALSE), or copy across with COLUMN().
2. Several criteria: join them in a helper column and look up the joined value.
3. In Microsoft 365, XLOOKUP does both more easily.