Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

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 60 days free trial!
combine sheets
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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?


quickly convert multiple XLSX. files to XLS or PDF. files in once time

In many cases, you may want to convert between XLSX and XLS, or convert a workbook to PDF file, but in Excel, you only can convert one workbook once time, have you ever imaged to convert multiple workbooks at once time? With Kutools for Excel's File Format Converter, you can quickly convert multiple workbook to pdf/97-2003 workbook/2007 or higher workbook at once and save them in a folder, at the same time, a converted report is generated!  Click for 60 days free trial!
doc file converter
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    pernellefpapapa · 4 months ago
    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
  • To post as a guest, your comment is unpublished.
    Frank · 8 months ago
    what you want to put the price in order of highest qty from column c?
  • To post as a guest, your comment is unpublished.
    gajanan · 8 months ago
    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
  • To post as a guest, your comment is unpublished.
    Spark Shit · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Adam · 2 years ago
    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).
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Sorry, I have not found any solutions about your problems. If you have found that, could you tell me? Thank u.
      • To post as a guest, your comment is unpublished.
        Rankee · 2 years ago
        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)))"
  • To post as a guest, your comment is unpublished.
    Abinaya · 2 years ago
    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
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Dani · 2 years ago
    Hello, i type the formula and excel gives an error that says that the formula does not have enough arguments, any help?
  • To post as a guest, your comment is unpublished.
    Rob Jones · 2 years ago
    Just a quick note to all, if you change it to be:

    =IFERROR(INDEX($B$2:$B$9, SMALL(IF($A$11=$A$2:$A$9, ROW($A$2:$A$9)-ROW($A$2)+1), COLUMN(A1))),"None")

    The #NUM will become none or use "" to get a blank cell! Looks much tidier!
  • To post as a guest, your comment is unpublished.
    Richard G · 4 years ago
    I have a similar problem but different.

    Vlookup Multiple Values - Return MULTIPLE corresponding values for MULTIPLE Lookup Values

    The formula I have searches for words in a text strings, starting with A1, then adds categories from a large list of categories in a table on ANOTHER WORKSHEET 'Dynamic Categories Lists' , depending on the words found in the A1 string. This is formula is in B1. The amount of data is huge 19,000 text strings in row A.


    For examples the text string might say:
    A B C
    1 dog has black spots Dalmation
    2 dog is tall Large Dog

    My formula searches for "black spots" and returns " Dalmatians " to B1
    My formula searches for " dog is tall" - my formula searches " tall " and return " large dogs" to B2

    Formula in B1 is:
    =PROPER(IFERROR(LOOKUP(1E+100,SEARCH('Dynamic Categories Lists'!$A$1:$A$1000,A1),'Dynamic Categories Lists'!$A$1:$A$1000),""))

    'Dynamic Categories Lists' (DIFFERENT WORKSHEET)
    A B
    1 Search Word to Find Categories: List Paste
    2 black spots Dalmation
    3 tall Large Dog
    4 short Small Dog
    5 -1000 MORE -1000 MORE


    My problem is I need to find the 2nd, 3rd, 4th occurrences

    Example
    A B C D
    1 dog has black spots Dalmation
    2 dog is tall Large Dog
    3
    4 dog has black spots and is tal Dalmation Large Dog


    A4 "dog has black spots and is tall" I want the formula to return "Dalmation" & "large dog" to B3

    Any help would be appreciated. I have searched heaps of threads and haven’t been able to find the answer!
  • To post as a guest, your comment is unpublished.
    Richard G · 4 years ago
    Thanks for your tips on

    I have a similar problem but different.

    Vlookup Multiple Values - Return MULTIPLE corresponding values for MULTIPLE Lookup Values

    The formula I have searches for words in a text strings, starting with A1, then adds categories from a large list of categories in a table on ANOTHER WORKSHEET 'Dynamic Categories Lists' , depending on the words found in the A1 string. This is formula is in B1. The amount of data is huge 19,000 text strings in row A.


    For examples the text string might say:
    A B C
    1 dog has black spots Dalmation
    2 dog is tall Large Dog

    My formula searches for "black spots" and returns " Dalmatians " to B1
    My formula searches for " dog is tall" - my formula searches " tall " and return " large dogs" to B2

    Formula in B1 is:
    =PROPER(IFERROR(LOOKUP(1E+100,SEARCH('Dynamic Categories Lists'!$A$1:$A$1000,A1),'Dynamic Categories Lists'!$A$1:$A$1000),""))

    'Dynamic Categories Lists' (DIFFERENT WORKSHEET)
    A B
    1 Search Word to Find Categories: List Paste
    2 black spots Dalmation
    3 tall Large Dog
    4 short Small Dog
    5 -1000 MORE -1000 MORE


    My problem is I need to find the 2nd, 3rd, 4th occurrences

    Example
    A B C D
    1 dog has black spots Dalmation
    2 dog is tall Large Dog
    3
    4 dog has black spots and is tal Dalmation Large Dog


    A4 "dog has black spots and is tall" I want the formula to return "Dalmation" & "large dog" to B3

    Any help would be appreciated. I have searched heaps of threads and haven’t been able to find the answer!
  • To post as a guest, your comment is unpublished.
    Summer · 4 years ago
    I have a spreadsheet with 115 client names in it that I pull values on each day from one tab to another using a vlookup. Another person in the office updates the values each day by pasting new values along with the corresponding client names. Client names are pasted on the 'PVAL' tab in column A and client values are pasted in column B. On the tab named Master we run a vlookup for the client names starting in cell N6 (the matching client name is listed in cell AB6). The vlookup is =VLOOKUP(AB6,PortDescPortVal,2,FALSE) Every day we update the spreadsheet there are the same 10 client names that will not automatically update. I have to go to the PVAL tab everyday and retype the client names for those 10 and then the data will update. It works for the rest of the clients every day. Also note that the person who updates the data has tried retyping their names for me but for some reason when she retypes their names the lookup still won't work. I can't find a setting in her Excel that differs from mine. I have ensured that all of the client names are in the array. I cannot for the life of me figure out what the problem is here and why I have to retype 10 out of 115 client names each and every day for the lookup to work. HELP!
    • To post as a guest, your comment is unpublished.
      guest · 1 years ago
      Try add columns (change to your ss range and get rid of the False argument like this; =VLOOKUP(AB6,'PortDescPortVal'!A:AA,2,)

      Maybe Refresh All in Data tab