Skip to main content

How to VLOOKUP and return multiple corresponding values horizontally in Excel?

doc-vlookup-horizontally-1
In default, VLOOKUP function can return multiple corresponding values in the vertical level in Excel, in some cases, you may want to return multiple values in horizontal level as below screenshot shown. Here I will tell you a formula can solve this task.
VLOOKUP and return multiple values horizontally

arrow blue right bubble VLOOKUP and return multiple values horizontally

For example, you have a range of data as below screenshot shown, and you want to VLOOKUP the prices of Apple.
doc-vlookup-horizontally-2

1. Select a cell and type this formula =INDEX($B$2:$B$9, SMALL(IF($A$11=$A$2:$A$9, ROW($A$2:$A$9)-ROW($A$2)+1), COLUMN(A1))) into to it, and then press Shift + Ctrl + Enter and drag the autofill handle to right to apply this formula until #NUM! appears. See screenshot:
doc-vlookup-horizontally-3

2. Then delete the #NUM!. See screenshot:
doc-vlookup-horizontally-4

Tip: In the above formula, B2:B9 is the column range which you want to return the values in, A2:A9 is the column range that the lookup value is in, A11 is the lookup value, A1 is the first cell of your data range, A2 is the first cell of the column range that you lookup value is in.

If you want to return multiple values vertically, you can read this article How to lookup value return multiple corresponding values in Excel?

Easily Combine multiple sheets/Workbook into one Single sheet or Workbook
To combinne multiples sheets or workbooks into one sheet or workbook may be edious in Excel, but with the Combine function in Kutools for Excel, you can combine merge dozens of sheets/workbooks into one sheet or workbook, also, you can consolidate the sheets into one by several clicks only.  Click for full-featured 30 days free trial!
combine sheets
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (20)
No ratings yet. Be the first to rate!
Computer
Computer
This comment was minimized by the moderator on the site
This article says this uses VLOOKUP, but it uses INDEX... which does not help me. I need a solution for multiple values using VLOOKUP or XLOOKUP in tables.
Reply
0
0
aj
aj     Computer
This comment was minimized by the moderator on the site
you can use TEXTSPLIT function followed by TEXTJOIN FUNCTION in excel 365
Reply
0
0
Tim
Tim
This comment was minimized by the moderator on the site
For anyone experiencing issues in a big data range, ensure you dont have any cells existent with "#N/A" or it will break it. Simple error but can be overlooked.
Reply
0
0
Hexa
Hexa
This comment was minimized by the moderator on the site
Hello, I want to use keyword instead of specific text or value here for multiple return values, can you please share code formula for same.
Reply
0
0
MW
MW
This comment was minimized by the moderator on the site
Hello - This formula worked perfectly - thank you! However, I want to apply it to a long list of lookup values. When I copy and paste the formula it obviously returns the same results as the first lookup value so I removed the $'s from $A$11, but that changes the result to something incorrect. How can I quickly apply this same formula for a long list of lookup values?

Thank you!

M
Reply
0
0
pernellefpapapa
pernellefpapapa
This comment was minimized by the moderator on the site
Bonjour,

J'ai retransposé mon problème exactement dans les mêmes positions de colonne que l'exemple et j’obtiens le message #VALEUR! dès la première case.

Mon excel fonctionne en francais, j'ai tout retranscrit en francais, passé ";"au lieu de ",", appuyé sur CTRL+MAJ+Entrée... des idées sur l'origine du problème?

Fanny
Reply
0
0
Frank
Frank
This comment was minimized by the moderator on the site
what you want to put the price in order of highest qty from column c?
Reply
0
0
gajanan
gajanan
This comment was minimized by the moderator on the site
i have to find vertical value first, in vertical value there are more horizontal value and i have choose grater value which i have. which function or formula can help
Reply
0
0
Spark Shit
Spark Shit
This comment was minimized by the moderator on the site
Hi. I have three google spreadsheet. First sheet is named "Summary. It is a table containing the name of our employee and number of evaluation in a week from 1-7. The next sheet is named "Chat Score"and third sheet is named Ëmail Score". What I want to to do is when we input a evaluation score to one of our employee it will be automatically recorded on the "Summary sheet" as evaluation 1 or 2 and so on and so forth. In short I want to get multiple return value in a single seach key across multiple sheet. I hope you can help. https://docs.google.com/spreadsheets/d/1lt-e4MxddUKg5xDt_0YchBiEgGe4mFKB-dHENwwtA6Y/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1lt-e4MxddUKg5xDt_0YchBiEgGe4mFKB-dHENwwtA6Y/edit?usp=sharing
Reply
0
0
Adam
Adam
This comment was minimized by the moderator on the site
Although I'm looking up my values in another worksheet (I don't think this should cause a problem?), I only seem to have success dragging to the right if I go in and change the +1 to '0' for the 1st instance of the lookup ID, '1' for the 2nd instance and so on. I know how many instances of the lookup ID I have so I can workaround. What do I need to change to allow me to successfully drag the fx across horizontally without needing to edit (i.e getting the #NUM to appear once there are no more lookup values to return).
Reply
0
0
Sunny
Sunny     Adam
This comment was minimized by the moderator on the site
Sorry, I have not found any solutions about your problems. If you have found that, could you tell me? Thank u.
Reply
Report
0
0
Rankee
Rankee     Sunny
This comment was minimized by the moderator on the site
Change Column to Row at the end of the formula

"=INDEX($B$1:$B$206, SMALL(IF($A$209=$A$1:$A$206, ROW($A$1:$A$206)-ROW($A$1)+1), ROW(A1)))"
Reply
0
0
Abinaya
Abinaya
This comment was minimized by the moderator on the site
How to List all values(like list of groceries bought) caused by a specific value (by a person X) using VLOOK_UP and other possible formulas
Reply
0
0
Sunny
Sunny     Abinaya
This comment was minimized by the moderator on the site
You mean that to list all grocerise a person need to buy, right? If so, you can refer to the formula in this article, and change the arguments as you need as below screenshot shown.
View Attachments (1 / 5)
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations