📎 This article includes 1 downloadable practice file ↓
Lookup Level: Intermediate Available in: Excel 2007+
In this article Syntax Examples Example 1 Example 2 Power combo Related functions INDEX returns the value at a given row (and optional column) of a range. Combined with MATCH it is the most flexible lookup in every Excel version.
Syntax
=INDEX(array, row_num, [column_num])
Argument
What it means
arrayThe range to pick from.
row_numRow position inside the range.
column_numColumn position (for 2-D ranges).
Examples
Example 1
=INDEX(B2:B100, 5)The 5th value in the list.
Example 2
=INDEX(C2:C500, MATCH(F2, A2:A500, 0))Classic INDEX/MATCH lookup.
Power combo
=INDEX(B2:M20, MATCH(P2, A2:A20, 0), MATCH(Q2, B1:M1, 0))Two-way lookup: row by region, column by month.
MATCH · XLOOKUP · VLOOKUP · OFFSET
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper .
📎 Practice files for this article 📗 Lookup scenarios workbook VLOOKUP, HLOOKUP, INDEX/MATCH and XLOOKUP side by side — vertical, horizontal, two-way — plus 6 real failure cases and their fixes, with PASS/FAIL checks.
⬇ XLSX · 10 KB Free to use for learning. Files with macros (.bas) are plain text — import them with Alt +F11 → File → Import File, and always test on a copy.
Written by Atul Vij Atul (AJ) builds office-automation and local-first AI tools as Atulya AI, and works with Excel, SAP and Oracle EPM every day. He writes about the shortcuts, macros and automations that actually save time — plus the occasional wacky fact.
More posts by Atul → ← Previous IFS Function in Excel: Several Conditions Without Nested IFsNext → LAMBDA Function in Excel: Create Your Own Excel Functions✨ Ask AI about this article Stuck on a step? Ask a question and the AI answers using this article.
Your question about this article Model Auto — best model for the task GPT-OSS 120B — OpenAI · strongest reasoning · counts as 2 DeepSeek R1 — DeepSeek · thinks step by step (slow) · counts as 2 Llama 3.3 70B — Meta · fast, all-round Qwen 2.5 Coder 32B — Alibaba · best for code Gemma 4 26B — Google · clear explanations Mistral Small 3.1 — Mistral · quick GPT-OSS 20B — OpenAI · balanced Llama 4 Scout — Meta · newest Llama Deepseek V4 Flash 0731 — Deepseek-ai Deepseek V4 Pro 0813 — Deepseek-ai · counts as 2 Gemma Sea Lion V4 27b — Aisingapore Glm 4.7 Flash — Zai-org Glm 5.2 — Zai-org · counts as 2 Glm 5.3 Flash — Zai-org Glm 5.3 — Zai-org · counts as 2 Kimi K2.6 — Moonshotai · counts as 2 Kimi K2.7 Code — Moonshotai · counts as 2 Llama 3.1 8b — Meta Llama 3.2 11b Vision — Meta Llama 3.2 3b — Meta Nemotron 3 120b A12b — Nvidia · counts as 2 Qwen3 30b A3b — Qwen Qwen3.8 27b — Qwen Qwq 32b — Qwen · counts as 2 Free · AI can be wrong Ask ✨
