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

**VLOOKUP to Return Multiple Values Horizontally in Excel: ****2 Methods**

**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)): 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.- Finally, the entire and combined formula extracts the first model name of Samsung devices from
**Column C**.

**Read More: **Find Max of Multiple Values by Using VLOOKUP Function in Excel

**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 Excel VLOOKUP to Return Multiple Values Vertically

**Download Practice Workbook**

You can download the Excel workbook that weâ€™ve used to prepare this article.

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

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”

in this exapmle only two brands are showing, suppose if i have mothen than 50 brands and i want to drag this formula vertially for all brands shall it work?

Hello SUNZEEV,

Thank you for your comment. Here, we are providing two revised formulas to meet your requirements.

For simplicity, we have modified the dataset with two additional Company names.

To get all the device names for each company, write the following formula in

E6and pressEnter.`=IFERROR(INDEX($B$5:$C$19,SMALL(IF($B$5:$B$19=E5,(ROW($B$5:$B$19)-ROWS(B1:B4)),""),(ROW($B$5:$B$19)-ROWS(B1:B4))),2),"")`

You will get all the results of

Samsungat once.Now, drag the

E6cell to the right toH6cell. You will get your desired results.Another way is to use the

FILTERfunction. For this method, write the following formula inE6and pressEnter.`=FILTER(C5:C19,B5:B19=E5)`

Now, drag the

E6cell to the right to theH6cell. You will get your desired results. TheFILTERfunction is available onExcel 365only.You can download the workbook from the link below.

Answer.xlsxWe hope these methods will satisfy your queries. If you have further questions, you are always welcome.

Regards,

Sourav

ExcelDemy.