VLOOKUP to Return Multiple Values Horizontally in Excel: 2 Methods

Method 1 – Use of INDEX, SMALL, and IF Functions to VLOOKUP and Return Corresponding Values Horizontally

Step 1:

➤ The required formula in cell C16 will be:

➤ After pressing Enter, you’ll get the first model name of Samsung from the table.

Step 2:

➤ Use Fill Handle from cell C16 and drag it rightward along Row 16 until a #NUM error appears.

➤ Skip the first #NUM error and stop auto-filling before that cell containing the error.

You’ll be shown all the model names of Samsung smartphones horizontally in the table.

  How Does the Formula Work?

• ROW($B$5:$B$14)-ROW($B$5)+1: This part is assigned to the second argument ([value_if_true]) of the IF function. It defines the row number of all data available in the range of cells B5:B14 and returns the following array:

{1;2;3;4;5;6;7;8;9;10}

• IF($B$16=$B$5:$B$14, ROW($B$5:$B$14)-ROW($B$5)+1): This part of the formula matches the criteria for Samsung devices only. If a match is found, the formula will return the perspective row number; otherwise, it’ll return FALSE. So, the overall return array from this formula will be:

{1;FALSE;3;FALSE;5;6;FALSE;FALSE;9;FALSE}

• SMALL(IF($B$16=$B$5:$B$14, ROW($B$5:$B$14)-ROW($B$5)+1), COLUMN(A1)): The SMALL function here extracts the lowest or smallest row number found from the previous step, and it’ll be defined as the second argument (row_num) of the INDEX function.
• The entire and combined formula extracts the first model name of Samsung devices from Column C.

Method 2 – VLOOKUP and Return Multiple Values Horizontally from a Sequence of Data in Excel

Step 1:

➤ In the output Cell C16, the required formula will be:

➤ Press Enter, and you’ll be displayed the first smartphone model name of Xiaomi immediately.

Step 2:

➤ Use Fill Handle to autofill rightward along Row 16 until a blank cell appears.

You’ll be displayed all the model names of the selected brand only like in the screenshot below.

  How Does the Formula Work?

• MATCH($B16,$B$5:$B$14,0): The MATCH function inside the INDEX function returns the first-row cell number containing the name- Xiaomi.
• MATCH($B16,$B$5:$B$14,0)+COLUMN()-3: This part is the second argument of the INDEX function, which defines the row number where the first resultant data will be looked for.
• INDEX($C$5:$C$14, MATCH($B16,$B$5:$B$14,0)+COLUMN()-3): This part is the second argument of the IF function ([value_if_TRUE]), which extracts the first output data based on the row number found in the previous step.
• The IF function will return a blank cell if no match is found.

 Note: To return data with this formula properly, you must initiate the table from Column B where Column B will represent the criteria and Column C will have the output data. You also have to define the selected criteria in Column B under or above the table as I’ve shown in Cell B16.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Related Articles

• How to Vlookup and Return Multiple Values in Drop Down List
• How to Use VLOOKUP Function on Multiple Rows in Excel
• How to VLOOKUP Multiple Values in One Cell in Excel
• Excel VLOOKUP to Return Multiple Values in One Cell Separated by Comma

<< Go Back to VLOOKUP Multiple Values | Excel VLOOKUP Function | Excel Functions | Learn Excel