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.

**Table of Contents**hide

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

`=INDEX($C$5:$C$14, SMALL(IF($B$16=$B$5:$B$14,ROW($B$5:$B$14)-ROW($B$5)+1), COLUMN(A1)))`

âž¤ 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:

`=IF(COLUMN()-2<=COUNTIF($B$5:$B$14,$B16), INDEX($C$5:$C$14,MATCH($B16,$B$5:$B$14,0)+COLUMN()-3),"")`

**âž¤** 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**

Hi, thanks for this post, I solved my problem on excel with this post. Thank you so much

Hi Michael,

Thanks for your feedback!

If you move the main lookup table to a new sheet this formulae doesn’t work

Hi KRISB,

I hope you are doing well.

If you copy the table and paste it somewhere else, then Excel will not adjust cell references according to the move.

The formula should work fine if you cut the table and paste it into another sheet.

For instance, I have cut the table and pasted it into “sheet1”. Then Excel automatically modifies the formula. See the image for better clarification.

The table was shifted in “sheet1”