How to Return Multiple Values from a Lookup in Excel

When you execute a standard VLOOKUP query in Microsoft Excel, the engine is mathematically hardcoded to stop scanning the exact millisecond it finds the first matching value. If your dataset contains multiple identical keys (e.g., an employee who has completed five different training courses), a standard VLOOKUP cannot extract the subsequent four courses. To force the Excel engine to violently extract every single geometric match into a continuous array, you must deploy an advanced INDEX and MATCH logic matrix.

Understanding the Multi-Value Array Architecture

Because VLOOKUP is structurally incapable of returning a multi-node array, we must hijack the INDEX function (which extracts data based on precise geometric coordinates) and combine it with the SMALL, IF, and ROW subroutines to mathematically calculate the coordinates of every single matching instance.

Executing the Array Extraction Vector

Imagine your data is in Sheet2!A1:B100. Column A contains Employee Names. Column B contains Course Names. John Smith appears three times in Column A. In Sheet1!A2, you have the target string “John Smith”. You want to extract all three courses into Sheet1!B2, C2, and D2.

To execute the complex multi-node extraction, click cell Sheet1!B2 and type this precise, highly advanced formula:

=IFERROR(INDEX(Sheet2!$B$1:$B$100, SMALL(IF(Sheet2!$A$1:$A$100=$A$2, ROW(Sheet2!$A$1:$A$100)-MIN(ROW(Sheet2!$A$1:$A$100))+1), COLUMNS($A$1:A1))), "")

CRITICAL STEP: In legacy versions of Excel, you cannot simply press Enter. You must mathematically force the software to evaluate this as an Array Formula by pressing Ctrl + Shift + Enter simultaneously. Modern Excel (Office 365) handles dynamic arrays natively.

Analyzing the Extraction Calculus

• The IF function executes a global sweep of Column A. Every time it finds “John Smith”, it records the absolute ROW number.
• The SMALL function intercepts this array of row numbers. Combined with the COLUMNS counter, it mathematically extracts the 1st smallest row number, then the 2nd smallest, etc., as you drag the formula sideways.
• The INDEX function takes that precise row coordinate and violently rips the corresponding Course data from Column B.
• Drag the formula across cells C2 and D2 to extract the subsequent matches. The IFERROR wrapper cleanly outputs a blank void if no further matches exist.