Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Vlookup in an array

This is a discussion on Vlookup in an array within the Excel Questions forums, part of the Question Forums category; Hi Iam trying to make a drop down list based on the result of a vlookup. What i want to ...

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    49

    Default Vlookup in an array

    Hi Iam trying to make a drop down list based on the result of a vlookup.
    What i want to do is look into a table that has country name, depot station, but i have more than one depot per country so when i look up with vlookup i only get one result back, the table looks like this.
    Country Country nameDepot code Depot name
    1 GBUnited Kingdom STN Stansted
    2 GBUnited Kingdom EDI Edinburgh
    3 GB United Kingdom EMA East midlands
    4 FR France
    GNO Garanoa
    5 FR France MRS Marseille

    How can i look up GB or United Kingdom and get all the depots listed from that country. Can anyone help please.

  2. #2
    Board Regular
    mmmm Pizza
    Sandeep Warrier's Avatar
    Join Date
    Oct 2008
    Location
    Mumbai, India
    Posts
    2,660

    Default Re: Vlookup in an array

    Hi,

    Is this what you are looking for?

    Sheet1
    ABCDEFGH
    1CountryCountry nameDepot codeDepot nameNameDepot Name
    2GBUnited KingdomSTNStanstedUnited KingdomStansted
    3GBUnited KingdomEDIEdinburghEdinburgh
    4GBUnited KingdomEMAEast midlandsEast midlands
    5FRFranceGNOGaranoa
    6FRFranceMRSMarseille
    Excel 2003

    Array Formulas
    CellFormula
    H2=IF(ROWS($H$1:H1)<=COUNTIF($B$2:$B$6,$G$2),INDEX($D$2:$D$6,SMALL(IF($B$2:$B$6=$G$2,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($H$1:H1))),"")
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


    Regards,
    Sandeep


    You can post sample data using any one of the following ways:

    1. Richard Schollar's HTML Maker
    2. Excel Jeanie
    3. Border Copy Paste

    Use code tags [CODE]'Your Code[/CODE]
    for posting codes.

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    49

    Default Re: Vlookup in an array

    Sorry go lost with this one, I only get Stansted returning in H2. What do you mean by below statement? I have cut and paste the formula into H2, then what ? sorry for sounding like a dumby on this one.

    Array Formulas
    CellFormula
    H2=IF(ROWS($H$1:H1)<=COUNTIF($B$2:$B$6,$G$2),INDEX($D$2:$D$6,SMALL(IF($B$2:$B$6=$G$2,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($H$1:H1))),"")
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


    [/QUOTE]

  4. #4
    Board Regular
    mmmm Pizza
    Sandeep Warrier's Avatar
    Join Date
    Oct 2008
    Location
    Mumbai, India
    Posts
    2,660

    Default Re: Vlookup in an array

    You need to copy the formula in the cells below. An easy way to do this would be to select a range that includes H2 and then press Ctrl+D

    Regards,
    Sandeep


    You can post sample data using any one of the following ways:

    1. Richard Schollar's HTML Maker
    2. Excel Jeanie
    3. Border Copy Paste

    Use code tags [CODE]'Your Code[/CODE]
    for posting codes.

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    49

    Default Re: Vlookup in an array

    Sorry to be a pain but tried that and get NUM!! in the cells below.
    What am i doing that so wrong i got Friday Brain block

    Cheers
    Sean

    Quote Originally Posted by sandeep.warrier View Post
    You need to copy the formula in the cells below. An easy way to do this would be to select a range that includes H2 and then press Ctrl+D


  6. #6
    Board Regular
    mmmm Pizza
    Sandeep Warrier's Avatar
    Join Date
    Oct 2008
    Location
    Mumbai, India
    Posts
    2,660

    Default Re: Vlookup in an array

    Ok... lets take this one step at a time...

    Do you get a proper result in H2?

    If yes, then copy H2, select a few cells below H2 and then paste.

    I'm leaving for home in 10 min so I won't be able to reply for another hour or 2.
    Regards,
    Sandeep


    You can post sample data using any one of the following ways:

    1. Richard Schollar's HTML Maker
    2. Excel Jeanie
    3. Border Copy Paste

    Use code tags [CODE]'Your Code[/CODE]
    for posting codes.

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,671

    Default Re: Vlookup in an array

    spiderjolly,

    sandeep.warrier's solution, and using VLOOKUP, and Function "VlookupNth" by jonmo1:
    http://www.mrexcel.com/board2/viewto...2c63523b1396b7



    Sheet1

     ABCDEFGH
    1CountryCountry nameDepot codeDepot name  NameDepot Name
    2GBUnited KingdomSTNStansted  United KingdomStansted
    3GBUnited KingdomEDIEdinburgh   Edinburgh
    4GBUnited KingdomEMAEast midlands   East midlands
    5FRFranceGNOGaranoa    
    6FRFranceMRSMarseille    
    7      United KingdomStansted
    8       Edinburgh
    9       East midlands
    10      GBStansted
    11       Edinburgh
    12       East midlands
    13      FranceGaranoa
    14       Marseille
    15      FRGaranoa
    16       Marseille
    17        

    Spreadsheet Formulas
    CellFormula
    H2{=IF(ROWS($H$1:H1)<=COUNTIF($B$2:$B$6,$G$2),INDEX($D$2:$D$6,SMALL(IF($B$2:$B$6=$G$2,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($H$1:H1))),"")}
    H3{=IF(ROWS($H$1:H2)<=COUNTIF($B$2:$B$6,$G$2),INDEX($D$2:$D$6,SMALL(IF($B$2:$B$6=$G$2,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($H$1:H2))),"")}
    H4{=IF(ROWS($H$1:H3)<=COUNTIF($B$2:$B$6,$G$2),INDEX($D$2:$D$6,SMALL(IF($B$2:$B$6=$G$2,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($H$1:H3))),"")}
    H7=VLOOKUP($G$7,$B$2:$D$6,3,FALSE)
    H8=Vlookupnth($G$7,$B$2:$D$6,3,2)
    H9=Vlookupnth($G$7,$B$2:$D$6,3,3)
    H10=VLOOKUP($G$10,$A$2:$D$6,4,FALSE)
    H11=Vlookupnth($G$10,$A$2:$D$6,4,2)
    H12=Vlookupnth($G$10,$A$2:$D$6,4,3)
    H13=VLOOKUP($G$13,$B$2:$D$6,3,FALSE)
    H14=Vlookupnth($G$13,$B$2:$D$6,3,2)
    H15=VLOOKUP($G$15,$A$2:$D$6,4,FALSE)
    H16=Vlookupnth($G$15,$A$2:$D$6,4,2)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4




    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Adding the Function
    1. Copy the below Function, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel


    Code:
    Option Explicit
    Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, Optional Nth As Long = 1)
    '
    ' jonmo1
    ' http://www.mrexcel.com/board2/viewtopic.php?t=302681&sid=81cc9a966bb7e6a4ca2c63523b1396b7
    '
    'Similar to Vlookup, but returns the Nth value found from the top of myrange.
    'Not necessarily the First.
    'The TRUE/FALSE argument usually found in Vlookup is assumed FALSE in this function.  Data does NOT need
    'to be sorted, and it searches for EXACT match.
    'if ColRef is omitted, uses the number of columns in myrange
    'if Nth is omitted, returns the first value found
    '
    ' VLOOKUP in cell K2 for the first entry
    ' =VLOOKUP($J$2,$B$2:$D$6,3,FALSE)
    '
    ' VlookupNth in cell F3:
    ' =VlookupNth($J$2,$B$2:$D$6,3,2)
    ' $J$2 is the value to search for
    ' $B$2:$D$6 is the range to search
    ' ,3, is the offset
    ' ,2) is the second entry to find
    '
    ' =VlookupNth($J$2,$B$2:$D$6,3,3)
    ' $J$2 is the value to search for
    ' $B$2:$D$6 is the range to search
    ' ,3, is the offset
    ' ,3) is the third entry to find
    '
    ' If there were were 4 entries to find
    ' =VlookupNth($J$2,$B$2:$D$6,3,4)
    '
    ' If there were were 5 entries to find
    ' =VlookupNth($J$2,$B$2:$D$6,3,5)
    '
    Dim Count, i As Long
    Dim MySheet As Worksheet
    Count = 0
    Set MySheet = Sheets(MyRange.Parent.Name)
    If ColRef = 0 Then ColRef = MyRange.Columns.Count
    For i = MyRange.Row To MyRange.Row + MyRange.Rows.Count - 1
      If MySheet.Cells(i, MyRange.Column).Value = MyVal Then
        Count = Count + 1
        If Count = Nth Then
          VlookupNth = MySheet.Cells(i, MyRange.Column + ColRef - 1).Value
          Exit Function
        End If
      End If
    Next i
    VlookupNth = ""
    End Function



    Function "VlookupNth" instructions:
    ' VLOOKUP in cell K2 for the first entry
    ' =VLOOKUP($J$2,$B$2:$D$6,3,FALSE)
    '
    ' VlookupNth in cell F3:
    ' =VlookupNth($J$2,$B$2:$D$6,3,2)
    ' $J$2 is the value to search for
    ' $B$2:$D$6 is the range to search
    ' ,3, is the offset
    ' ,2) is the second entry to find
    '
    ' =VlookupNth($J$2,$B$2:$D$6,3,3)
    ' $J$2 is the value to search for
    ' $B$2:$D$6 is the range to search
    ' ,3, is the offset
    ' ,3) is the third entry to find
    '
    ' If there were were 4 entries to find
    ' =VlookupNth($J$2,$B$2:$D$6,3,4)
    '
    ' If there were were 5 entries to find
    ' =VlookupNth($J$2,$B$2:$D$6,3,5)
    Have a great day,
    hiker95

  8. #8
    New Member
    Join Date
    Oct 2009
    Posts
    49

    Default Re: Vlookup in an array

    Sorry mate I really struggling with this. It should be so simple but I keep getting a response in H3 #NUM! instead of the response Edinburgh and the same for the cells below that. Have checked cell formats and they are same. So really cant see what the problem is

    Cheers
    Sean



    Quote Originally Posted by hiker95 View Post
    spiderjolly,

    sandeep.warrier's solution, and using VLOOKUP, and Function "VlookupNth" by jonmo1:
    http://www.mrexcel.com/board2/viewto...2c63523b1396b7


    Sheet1

    ABCDEFGH
    1CountryCountry nameDepot codeDepot name NameDepot Name
    2GBUnited KingdomSTNStansted United KingdomStansted
    3GBUnited KingdomEDIEdinburgh Edinburgh
    4GBUnited KingdomEMAEast midlands East midlands
    5FRFranceGNOGaranoa
    6FRFranceMRSMarseille
    7 United KingdomStansted
    8 Edinburgh
    9 East midlands
    10 GBStansted
    11 Edinburgh
    12 East midlands
    13 FranceGaranoa
    14 Marseille
    15 FRGaranoa
    16 Marseille
    17

    Spreadsheet Formulas
    CellFormula
    H2{=IF(ROWS($H$1:H1)<=COUNTIF($B$2:$B$6,$G$2),INDEX($D$2:$D$6,SMALL(IF($B$2:$B$6=$G$2,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($H$1:H1))),"")}
    H3{=IF(ROWS($H$1:H2)<=COUNTIF($B$2:$B$6,$G$2),INDEX($D$2:$D$6,SMALL(IF($B$2:$B$6=$G$2,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($H$1:H2))),"")}
    H4{=IF(ROWS($H$1:H3)<=COUNTIF($B$2:$B$6,$G$2),INDEX($D$2:$D$6,SMALL(IF($B$2:$B$6=$G$2,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($H$1:H3))),"")}
    H7=VLOOKUP($G$7,$B$2:$D$6,3,FALSE)
    H8=Vlookupnth($G$7,$B$2:$D$6,3,2)
    H9=Vlookupnth($G$7,$B$2:$D$6,3,3)
    H10=VLOOKUP($G$10,$A$2:$D$6,4,FALSE)
    H11=Vlookupnth($G$10,$A$2:$D$6,4,2)
    H12=Vlookupnth($G$10,$A$2:$D$6,4,3)
    H13=VLOOKUP($G$13,$B$2:$D$6,3,FALSE)
    H14=Vlookupnth($G$13,$B$2:$D$6,3,2)
    H15=VLOOKUP($G$15,$A$2:$D$6,4,FALSE)
    H16=Vlookupnth($G$15,$A$2:$D$6,4,2)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4




    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Adding the Function
    1. Copy the below Function, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel


    Code:
     
    Option Explicit
    Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, Optional Nth As Long = 1)
    '
    ' jonmo1
    ' http://www.mrexcel.com/board2/viewtopic.php?t=302681&sid=81cc9a966bb7e6a4ca2c63523b1396b7
    '
    'Similar to Vlookup, but returns the Nth value found from the top of myrange.
    'Not necessarily the First.
    'The TRUE/FALSE argument usually found in Vlookup is assumed FALSE in this function.  Data does NOT need
    'to be sorted, and it searches for EXACT match.
    'if ColRef is omitted, uses the number of columns in myrange
    'if Nth is omitted, returns the first value found
    '
    ' VLOOKUP in cell K2 for the first entry
    ' =VLOOKUP($J$2,$B$2:$D$6,3,FALSE)
    '
    ' VlookupNth in cell F3:
    ' =VlookupNth($J$2,$B$2:$D$6,3,2)
    ' $J$2 is the value to search for
    ' $B$2:$D$6 is the range to search
    ' ,3, is the offset
    ' ,2) is the second entry to find
    '
    ' =VlookupNth($J$2,$B$2:$D$6,3,3)
    ' $J$2 is the value to search for
    ' $B$2:$D$6 is the range to search
    ' ,3, is the offset
    ' ,3) is the third entry to find
    '
    ' If there were were 4 entries to find
    ' =VlookupNth($J$2,$B$2:$D$6,3,4)
    '
    ' If there were were 5 entries to find
    ' =VlookupNth($J$2,$B$2:$D$6,3,5)
    '
    Dim Count, i As Long
    Dim MySheet As Worksheet
    Count = 0
    Set MySheet = Sheets(MyRange.Parent.Name)
    If ColRef = 0 Then ColRef = MyRange.Columns.Count
    For i = MyRange.Row To MyRange.Row + MyRange.Rows.Count - 1
      If MySheet.Cells(i, MyRange.Column).Value = MyVal Then
        Count = Count + 1
        If Count = Nth Then
          VlookupNth = MySheet.Cells(i, MyRange.Column + ColRef - 1).Value
          Exit Function
        End If
      End If
    Next i
    VlookupNth = ""
    End Function



    Function "VlookupNth" instructions:
    ' VLOOKUP in cell K2 for the first entry
    ' =VLOOKUP($J$2,$B$2:$D$6,3,FALSE)
    '
    ' VlookupNth in cell F3:
    ' =VlookupNth($J$2,$B$2:$D$6,3,2)
    ' $J$2 is the value to search for
    ' $B$2:$D$6 is the range to search
    ' ,3, is the offset
    ' ,2) is the second entry to find
    '
    ' =VlookupNth($J$2,$B$2:$D$6,3,3)
    ' $J$2 is the value to search for
    ' $B$2:$D$6 is the range to search
    ' ,3, is the offset
    ' ,3) is the third entry to find
    '
    ' If there were were 4 entries to find
    ' =VlookupNth($J$2,$B$2:$D$6,3,4)
    '
    ' If there were were 5 entries to find
    ' =VlookupNth($J$2,$B$2:$D$6,3,5)

  9. #9
    New Member
    Join Date
    Oct 2009
    Posts
    49

    Default Re: Vlookup in an array

    Guys what i am looking for is, if i select a country from a drop down list in sheet then the cell next to it should display the depots attached to the country, be that the at country name level or country level. Does this make my request even more complicated or clearer?

    Cheers
    Sean



    QUOTE=spiderjolly;2236468]Sorry mate I really struggling with this. It should be so simple but I keep getting a response in H3 #NUM! instead of the response Edinburgh and the same for the cells below that. Have checked cell formats and they are same. So really cant see what the problem is

    Cheers
    Sean[/QUOTE]

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,671

    Default Re: Vlookup in an array

    spiderjolly,

    Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
    http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


    Where to upload Excel files so threads don't take 20+ posts to get the correct answer from guessing:
    MediaFire: http://www.mediafire.com/
    FileFactory: http://www.filefactory.com/
    FileSavr: http://www.filesavr.com/
    FileDropper: http://www.filedropper.com/
    Or, you can upload it to www.box.net and provide us with a link to your workbook.
    Have a great day,
    hiker95

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com