VLOOKUP to Return Multiple Values Horizontally in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

use of index if small functions to vlookup and return multiple values horizontally in excel

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

use of index if small functions to vlookup and return multiple values horizontally in excel

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

use of index if small functions to vlookup and return multiple values horizontally in excel

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

vlookup and return multiple values horizontally in excel from a sequence of data

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

vlookup and return multiple values horizontally in excel from a sequence of data

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

vlookup and return multiple values horizontally in excel from a sequence of data

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


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

6 Comments
  1. Reply
    Michael Enrique Masis Portilla Oct 14, 2021 at 4:23 AM

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

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

  3. 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.
    1
    The table was shifted in “sheet1”
    2

  4. 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 E6 and press Enter.
      =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 Samsung at once.
      Now, drag the E6 cell to the right to H6 cell. You will get your desired results.

      Another way is to use the FILTER function. For this method, write the following formula in E6 and press Enter.
      =FILTER(C5:C19,B5:B19=E5)
      Now, drag the E6 cell to the right to the H6 cell. You will get your desired results. The FILTER function is available on Excel 365 only.

      You can download the workbook from the link below.
      Answer.xlsx
      We hope these methods will satisfy your queries. If you have further questions, you are always welcome.

      Regards,
      Sourav
      ExcelDemy.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo