Vlookup in an array

spiderjolly

Board Regular
Joined
Oct 20, 2009
Messages
58
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.
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> Country </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> Country name</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Depot code </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Depot name </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> 1</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> GB</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">United Kingdom </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> STN</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> Stansted</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> 2</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> GB</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">United Kingdom </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> EDI</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> Edinburgh</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> 3</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> GB</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> United Kingdom</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> EMA</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> East midlands</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> 4</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> FR</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> France
</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> GNO</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> Garanoa</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> 5</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> FR</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> France</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> MRS</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> Marseille</TD></TR></TBODY></TABLE>
How can i look up GB or United Kingdom and get all the depots listed from that country. Can anyone help please.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi,

Is this what you are looking for?
Excel Workbook
ABCDEFGH
1CountryCountry nameDepot codeDepot nameNameDepot Name
2GBUnited KingdomSTNStanstedUnited KingdomStansted
3GBUnited KingdomEDIEdinburghEdinburgh
4GBUnited KingdomEMAEast midlandsEast midlands
5FRFranceGNOGaranoa
6FRFranceMRSMarseille
Sheet1
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
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.
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself<INPUT id=gwProxy type=hidden><!--Session data--><INPUT id=jsProxy type=hidden *******="jsCall();">[/QUOTE]
 
Upvote 0
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

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
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

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

<INPUT id=gwProxy type=hidden><!--Session data--><INPUT id=jsProxy type=hidden *******="jsCall();"><INPUT id=gwProxy type=hidden><!--Session data--><INPUT id=jsProxy type=hidden *******="jsCall();"><INPUT id=gwProxy type=hidden><!--Session data--><INPUT id=jsProxy type=hidden *******="jsCall();">
 
Upvote 0
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.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
spiderjolly,

sandeep.warrier's solution, and using VLOOKUP, and Function "VlookupNth" by jonmo1:
http://www.mrexcel.com/board2/viewtopic.php?t=302681&sid=81cc9a966bb7e6a4ca2c63523b1396b7



Excel Workbook
ABCDEFGH
1CountryCountry nameDepot codeDepot nameNameDepot Name
2GBUnited KingdomSTNStanstedUnited KingdomStansted
3GBUnited KingdomEDIEdinburghEdinburgh
4GBUnited KingdomEMAEast midlandsEast midlands
5FRFranceGNOGaranoa
6FRFranceMRSMarseille
7United KingdomStansted
8Edinburgh
9East midlands
10GBStansted
11Edinburgh
12East midlands
13FranceGaranoa
14Marseille
15FRGaranoa
16Marseille
17
Sheet1





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)
 
Upvote 0
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



spiderjolly,

sandeep.warrier's solution, and using VLOOKUP, and Function "VlookupNth" by jonmo1:
http://www.mrexcel.com/board2/viewtopic.php?t=302681&sid=81cc9a966bb7e6a4ca2c63523b1396b7


Excel Workbook
ABCDEFGH
1CountryCountry nameDepot codeDepot nameNameDepot Name
2GBUnited KingdomSTNStanstedUnited KingdomStansted
3GBUnited KingdomEDIEdinburghEdinburgh
4GBUnited KingdomEMAEast midlandsEast midlands
5FRFranceGNOGaranoa
6FRFranceMRSMarseille
7United KingdomStansted
8Edinburgh
9East midlands
10GBStansted
11Edinburgh
12East midlands
13FranceGaranoa
14Marseille
15FRGaranoa
16Marseille
17
Sheet1





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)
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top