FormulaCraft

How to combine ARRAYFORMULA with VLOOKUP in Excel and Google Sheets

Topic:VLOOKUP

Heads up: Excel and Google Sheets do this differently.

Excel
=VLOOKUP(A2:A4,C2:D4,2,0)
Google Sheets
=ARRAYFORMULA(VLOOKUP(A2:A4,C2:D4,2,0))

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

Step by step

  1. 1Set up a lookup table with IDs in the first column and the return values in the next column (e.g., C2:D6).
  2. 2In Google Sheets, type =ARRAYFORMULA(VLOOKUP(A2:A100,C:D,2,0)) in the first result cell — it fills the entire column automatically.
  3. 3In Excel 365, type =VLOOKUP(A2:A100,C:D,2,0) and press Enter; the results spill into the rows below automatically.
  4. 4In older Excel versions, enter the formula and press Ctrl+Shift+Enter to make it a legacy array formula, or copy it down manually.

Tips

Need the Google Sheets version instead? Open Sheets variant in workspace →

Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.

Frequently asked

Why does my ARRAYFORMULA VLOOKUP return #N/A for empty rows?

Use IFERROR or limit the range to only rows with data: ARRAYFORMULA(IF(A2:A100<>"",VLOOKUP(A2:A100,C:D,2,0),"")).

Is XLOOKUP better than VLOOKUP with ARRAYFORMULA?

Yes — in both Excel 365 and Google Sheets, XLOOKUP is array-aware by default, handles missing values more gracefully, and doesn't require ARRAYFORMULA wrapping.

More on VLOOKUP

See all →
Reference

VLOOKUP

Look up a value in the first column of a range and return a value from another column in the same row.

How-to

How to VLOOKUP from another sheet

Pull a value from a different tab with VLOOKUP by prefixing the range with the sheet name. Works in Excel and Google Sheets.

Error fix

Fix #N/A

Fix #N/A errors in VLOOKUP in Excel and Google Sheets — covers missing matches, extra spaces, data-type mismatches, and range mistakes.

How-to

How to VLOOKUP with multiple criteria

Look up a value that matches two or more conditions using INDEX/MATCH with joined keys. Works in Excel 365 and Google Sheets.

Error fix

Fix Wrong value

VLOOKUP returns an incorrect value in Excel and Google Sheets — fix wrong column index, approximate match mode, unsorted data, and duplicate lookup keys.

How-to

How to fix VLOOKUP returning #N/A

VLOOKUP shows #N/A when it can’t find a match. Here are the real causes — exact-match, spaces, text-vs-number — and how to fix each.

Formulas used

VLOOKUPARRAYFORMULA (Google Sheets)XLOOKUPHLOOKUPCOUNTIFSVSTACKHSTACKAGGREGATE

Related tasks

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: