VLOOKUP in Excel for Multiple Columns: Methods and Tips

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365, VLOOKUP can return values from multiple columns either by copying one formula across the output row or by using an array of column numbers to spill several results at once. For a simpler formula that returns a whole adjacent range, XLOOKUP is another option.

What “multiple columns” can mean

The question can describe two different tasks. You may want to find one record using a key such as an employee ID and return several fields from that record—for example, a name, department, and salary. Or you may want to search using more than one condition, such as a customer ID and a region. VLOOKUP handles the first task directly; the second needs a workaround or a different lookup formula.

VLOOKUP’s name reflects its basic design: it searches vertically down the first column of a table and returns a value from a specified column in the same row. Its column-index argument accepts one column number in the ordinary formula. That does not mean you must create a separate lookup for every result, however. You can fill a formula across, or, in modern Excel, supply several column numbers as an array.

In the examples below, the source table has IDs in column A, names in B, departments in C, and annual sales in D. The ID to search for is in F2. Change the ranges and columns to match your own workbook.

Use one VLOOKUP formula and copy it across

This is often the easiest method to understand and maintain. Start the output in G2, then copy the formula into the cells to its right. The dollar signs keep the lookup value and source table fixed, while the column number changes as the formula moves across.

With IDs in A2:A100, names in B2:B100, departments in C2:C100, sales in D2:D100, and the search ID in F2, enter this in G2:

=VLOOKUP($F2,$A$2:$D$100,COLUMN(B1),FALSE)

Copy G2 right through I2. In G2, COLUMN(B1) returns 2, so VLOOKUP returns the name from the second table column. In H2, the reference shifts to C1 and returns 3, the department column. In I2, it shifts to D1 and returns 4, the sales column. For an ID whose row contains Maya Chen, Operations, and 58000, the three output cells show those values in that order.

The final argument, FALSE, requests an exact match. This matters for IDs, names, and most other keys: omitting the argument can invoke approximate-match behavior, which is generally unsuitable for an ordinary record lookup. Keep the lookup key in the leftmost column of the selected table range because VLOOKUP cannot search to the left of that column.

To reuse the formula for additional records, copy the output row down. The source range remains fixed, and the lookup reference changes from F2 to F3, F4, and so on. On Windows, you can select the formula and adjacent destination cells and press Ctrl+R to fill right. On Mac, the equivalent shortcut is Control+R. You can also drag the fill handle at the lower-right corner of the selected cell.

Return several columns with a spill formula

Excel for Microsoft 365 can also evaluate an array of column numbers and spill the results into neighboring cells. This can be convenient when you want one formula rather than a formula copied into every output cell.

With the same table layout—IDs in A2:A100, return fields in B:D, and the requested ID in F2—enter the following in G2:

=VLOOKUP(F2,A2:D100,{2,3,4},FALSE)

In a version of Excel that supports dynamic arrays, the result spills horizontally into G2:I2: name, department, and sales. Make sure those destination cells are empty. If something is in the spill area, Excel displays a #SPILL! error until you clear or move the blocking content. The spilled results are controlled by the formula in G2, so you edit that one formula rather than editing each result cell independently.

Array constants use braces and commas in the English-language formula syntax shown here. Spreadsheet regional settings can affect which separators Excel expects. If Excel rejects the formula, check the list separator used by your installation and enter the array constant using the appropriate regional syntax. The copy-across method avoids an array constant and is a dependable alternative.

How VLOOKUP’s column number works

The third VLOOKUP argument counts columns from the first column of the table range, not from worksheet column A. For example, if the table range is A2:D100, A is column 1 and D is column 4. If you instead select B2:D100, B becomes column 1, C becomes 2, and D becomes 3. This is a frequent source of incorrect results when a formula’s range changes.

In the copy-across formula, COLUMN(B1) provides the changing index: it evaluates to 2 in the first output cell, then 3 and 4 as you copy right. The argument is not looking up a worksheet value; it is calculating the number of the return column. A fixed number such as 2 would keep returning the same table column in every output cell.

VLOOKUP returns values from one matching row. If the lookup key occurs more than once, an exact-match VLOOKUP returns the first matching row from top to bottom; it does not return every matching record. If your key should be unique, duplicate keys can therefore make the result look valid while pointing to the wrong record. Check that the key column is unique when that matters.

Use XLOOKUP when it fits better

For a new workbook, XLOOKUP is often more straightforward when the goal is to return several adjacent columns. It separates the lookup range from the return range, so the lookup column does not have to be the leftmost column of a larger table. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.

With IDs in A2:A100, the three return columns in B2:D100, and the search ID in F2, enter this in G2:

=XLOOKUP(F2,A2:A100,B2:D100,"Not found")

The matching row’s values from B through D spill into the cells to the right. The optional fourth argument supplies a message when the ID is missing. As with the VLOOKUP spill method, the destination cells must be clear. XLOOKUP also avoids a fragile numeric column index: inserting a column within a VLOOKUP table can make a hard-coded index return a different field, whereas XLOOKUP’s explicit return range makes the intended fields easier to see.

VLOOKUP remains useful when a workbook already uses it, when compatibility with older Excel versions is important, or when you need to follow an existing template. If you share a file with someone using an older version, verify that the recipient’s Excel supports any newer functions or dynamic-array behavior used in it.

When the lookup uses more than one condition

VLOOKUP does not have separate arguments for two independent criteria. A common approach is to create a helper column that combines the criteria, then look up that combined key. For example, a customer ID alone may appear in multiple regions, so the intended record is identified by both customer ID and region. A delimiter between combined values can help make the key readable, but choose one that cannot be confused with characters in the source values.

If customer IDs are in A2:A100, regions in B2:B100, values to return in C2:D100, the requested customer ID is in F2, and the requested region is in G2, this XLOOKUP formula matches both conditions and returns columns C:D:

=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:D100,"Not found")

Each comparison produces a series of TRUE or FALSE values; multiplying the two series makes a 1 where both conditions are true and 0 elsewhere. XLOOKUP searches for 1. This approach requires Microsoft 365 or Excel 2021 or later. It still returns the first matching row if multiple rows satisfy both conditions, so confirm that the combined criteria identify the record you actually want.

Common errors and practical checks

  • #N/A: No exact match was found. Check for extra spaces, different data types, or a key that is absent from the lookup column. A number stored as text may not match a numeric key.
  • #REF!: The column index is larger than the number of columns in the selected table range, or a referenced range was removed. Count the range’s columns from its left edge.
  • Wrong field returned: Recheck the table range and column index. The index starts at the first column of the selected range, not at the worksheet’s column number.
  • Repeated or unexpected result: Look for duplicate keys and confirm that the formula uses FALSE for exact matching. VLOOKUP returns the first match it encounters.
  • #SPILL!: For a multi-column array result, clear cells that block the intended output. Also check whether merged cells or a formula placed inside an Excel table are interfering with the spill area.

For IDs with inconsistent spaces, clean the source values rather than masking a data-quality problem in every lookup formula. Also verify that both sides of a key use the same underlying type. Applying a number format changes how a value looks, but does not necessarily convert text into a number.

Frequently Asked Questions

Can VLOOKUP return multiple columns at once?
Yes. In Microsoft 365, a VLOOKUP with an array of column numbers can spill several results, or you can copy a formula with a changing column index across. XLOOKUP can return an adjacent multi-column range directly.

Can I drag VLOOKUP across columns?
Yes. Use a column index that changes as the formula moves, such as COLUMN(B1), and anchor the lookup value and table range with dollar signs. Copy the formula across the output cells and check that the returned fields are in the intended order.

Why does VLOOKUP return only one value?
A standard VLOOKUP formula has one column index and returns one value. To get several fields, copy it across with changing indexes or use a supported array form that returns multiple columns.

Can VLOOKUP search for a value in a column to the right?
No. VLOOKUP searches only the first column of its table range and returns values from columns to its right. Use XLOOKUP, or another lookup method, when the return field is to the left of the search field.

Does VLOOKUP return every row with a matching key?
No. It returns the first match it finds. To list all matching records, use a filtering approach such as FILTER in Microsoft 365 or Excel 2021 or later, rather than VLOOKUP.

What should I use: VLOOKUP or XLOOKUP?
Use VLOOKUP when you need compatibility with older workbooks or a familiar method. For new Microsoft 365 work, XLOOKUP is often clearer because it names the lookup and return ranges separately and can return multiple adjacent columns.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.