If you want to learn how to use VLOOKUP to return multiple values horizontally in Excel, we’ve got you covered right here.
The VLOOKUP function in Excel is traditionally used for vertical lookup. This means it is designed to search for a value in the first column of a table and return a value in the same row from a specified column. However, VLOOKUP is limited in that it will only return one value for each lookup. If you have a scenario where you need to retrieve multiple values that are all associated with a single lookup value (such as multiple book titles for one author), VLOOKUP cannot do this by itself.
For instance, if you use VLOOKUP to search for “Lewis” in a list of authors, it will find “Lewis” and can return the first book title associated with “Lewis”. But if “Lewis” has written multiple books, VLOOKUP won’t return the subsequent titles; it’s not capable of returning a horizontal array of all “Lewis” titles. You’d need a more complex array formula or a combination of other functions to achieve that, as VLOOKUP is not designed for horizontal array output or multiple results for a single query.
But don’t worry, there are two ways to use VLOOKUP to return multiple values horizontally in Excel. Think of them as secret moves that let you collect and line up all the toys you want without getting stuck with duplicates or a messy pile. We’ll show you how to use these moves in Excel, making it super easy to get what you need, neatly arranged just the way you want.
Method 1 – Using VLOOKUP to fetch and display multiple horizontal values from a data sequence in Excel
We need to extract all book titles by a specific author, “Lewis,” and display them horizontally in an Excel sheet. Using a combination of IF, COLUMN, COUNTIF, INDEX, and MATCH functions, I’ll write a formula that populates the titles in a row based on the author’s name.
But first before writing our formula, these are the things to keep in mind:
Identify your data range
First, identify the data range that includes the authors and book titles. In our example, the authors are in column B and the book titles are in column C, starting from row 4 to row 12.
Enter the lookup value
Enter the lookup value, “Rowling”, in a cell where you want to start displaying the results. In the example, this is cell F6.
Write the formula
In cell F6, enter the formula that will search for “Rowling” in the Author column and return the corresponding Book Title.
Now, let’s have a look at our dataset:
Select Cell F6 in our case and insert the following formula:
=IF(COLUMN()-COLUMN($E$6)<=COUNTIF($B$5:$B$13,$E6),INDEX($C$5:$C$13,MATCH($E6,$B$5:$B$13,0)+COLUMN()-COLUMN($E$6)-1),””)
After inserting the formula press Enter and then drag from the corner of cell F6 towards your left to get the next value.
Now, let’s understand the Formula:
- COLUMN() – COLUMN($E$6): This calculates how many columns to the right of E6 the current cell is. For example, if you’re in column F, this part would return 1 because F is one column away from E.
- COUNTIF($B$5:$B$13, $E6): This counts how many times the author’s name in cell E6 appears in the list from B5 to B13.
- IF(… <= …, …, “”): The IF function checks if the number from step 1 is less than or equal to the count from step 2. If yes, it means there are still book titles to display; if no, it means there are no more titles and it returns an empty string (“”).
- MATCH($E6,$B$5:$B$13,0): This finds the first occurrence of the author’s name from E6 in the range B5:B13 and returns its relative position.
- MATCH(…) + COLUMN() – COLUMN($E$6) – 1: This calculates the position of the next book title by adding the number of columns away from E6 to the position found in step 4, and then subtracting 1 to adjust for the array starting at 0.
- INDEX($C$5:$C$13, …): This gets the book title from column C which corresponds to the number calculated in step 5.
Put together, the formula displays the next book title for the author in E6 each time it’s filled rightward into a new column, until there are no more titles to list.
Method 2 – Easy horizontal lookup using INDEX, SMALL, and IF functions for quick data retrieval
To demonstrate how to use VLOOKUP to return multiple values using INDEX, SMALL, and IF from data in Excel, we’ll follow a step-by-step approach.
Let’s consider a book inventory where we have a list of authors and their respective book titles. We want to retrieve all book titles for the author ‘Rowling’ and display them horizontally. Before getting into the examples let’s discuss some factors in order to write a formula for retrieving horizontal formula:
- Select Cell D14, This is the cell where you want to display the horizontal list of books from the author (i.e Rowling)
- Enter the following formula:
=INDEX($C$5:$C$14, SMALL(IF($B$16=$B$5:$B$14,ROW($B$5:$B$14)-ROW($B$5)+1), COLUMN(A1)))
First, let’s understand the formula:
- ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1: This part calculates the row numbers relative to the range B4:B12 where the authors are listed. Since Excel’s ROW function returns the absolute row number, we subtract the row number of the first cell in the range (MIN(ROW($B$4:$B$12)), which is 4 in this case) and add 1 to normalize the array, starting from 1. This gives us an array like this: {1;2;3;4;5;6;7;8;9}
- IF($B$14=$B$4:$B$12, ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1): Here, the formula checks each cell in the range B4:B12 to see if it matches the author’s name in B14 (“Rowling”). If a match is found, it returns the relative row number from the array we calculated in the previous step; otherwise, it returns FALSE. The resulting array might look something like this if “Rowling” is found in the first, third, and fourth positions within the range:{1;FALSE;3;4;FALSE;FALSE;FALSE;FALSE;FALSE}
- SMALL(IF($B$14=$B$4:$B$12, ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1), COLUMN(A1)): The SMALL function is used to find the k-th smallest value from the array returned by the IF function, where k is given by COLUMN(A1). This is effectively looking for the smallest (first) relative row number where the condition is TRUE. As you copy this formula to the right, the k value will increase because COLUMN(A1) will increment (in cell D15, it becomes COLUMN(B1) which equals 2, etc.), finding the second, third, and subsequent matches.
- INDEX($C$4:$C$12, SMALL(IF($B$14=$B$4:$B$12, ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1), COLUMN(A1))): Finally, the entire combined formula uses the INDEX function to retrieve the value from the range C4:C12 (book titles) at the row number provided by the SMALL function. For the first cell where this formula is entered, it will extract the first book title by “Rowling” from column C.
Conclusion
This was everything you needed to know about how to use VLOOKUP to return multiple values horizontally in Excel. The methods we’ve talked about will help you line up your Excel data side by side, making it easier to look at and understand. By getting to know how these Excel tricks work, you can make your work with numbers and info much simpler. These tips are really useful, especially when you need to sort out lots of data or when you’re trying to get a better picture of what your data is telling you.
Learn more about the VLOOKUP function in Excel through these guides:
