VLOOKUP to Return Multiple Values Horizontally in Excel

In Microsoft Excel, there are a few options to VLOOKUP and return multiple values horizontally. Although the VLOOKUP function itself is not able to return multiple values horizontally, you’ll get introduced to the alternative options in this article with proper illustrations.

Download Practice Workbook

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

2 Methods to VLOOKUP and Return Multiple Values Horizontally

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from the specified column. You cannot extract multiple values by using the VLOOKUP function at once. If you drag down the output cell to generate the following results, you’ll get duplicate values for some data. And after all, you won’t be able to display the extracted data horizontally with just the VLOOKUP function in Excel. So, there are two alternatives we can apply in the Excel spreadsheet to serve our purposes.

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

In the following picture, the table represents a number of smartphone models with their brand names. As they are in random order, we’ll extract the model names of a specific brand and show them horizontally in a row.

For example, we want to display the model names of the smartphone brand- Samsung in Row 16.

📌 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:

➤ Now 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.

And you’ll be shown all the model names of Samsung smartphones horizontally that are available in the given 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)): 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.
• Finally, the entire and combined formula extracts the first model name of Samsung devices from Column C.

Read More: Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)

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

Now let’s consider another scenario. In the following table, Column B has two smartphone brands in an arranged order. Like before, we’ll extract the model names of one specific brand but with a different formula. And this formula will work for a sequential range of data only. If the brand names are in random order as shown in the previous section then this formula won’t work out appropriately.

For example, we’ll extract the model names of Xiaomi smartphones only and show them horizontally in Row 16.

📌 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 right away.

📌 Step 2:

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

And 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 number of the cell 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.
• If no match is found, the IF function will return a blank cell.

💭 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.

Read More: How to Use VLOOKUP Function in Excel VBA (4 Examples)

Concluding Words

I hope these two methods mentioned above will now help you to apply them in your Excel spreadsheet by extracting data from a table and showing them horizontally in a row. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Related Articles

• VLOOKUP and Return All Matches in Excel (7 Ways)
• How to Vlookup and Return Multiple Values in Drop Down List
• Use Excel VBA VLOOKUP to Find Values in Another Worksheet
• Excel VLOOKUP Function to Return Min Value from Multiple Hits
• VLOOKUP with Multiple Criteria in Excel (6 Examples)
• How to Use VLOOKUP Function on Multiple Rows in Excel
• Use VLOOKUP to Find Multiple Values with Partial Match in Excel