spiderjolly,
sandeep.warrier's solution, and using VLOOKUP, and Function "VlookupNth" by jonmo1:
http://www.mrexcel.com/board2/viewto...2c63523b1396b7
Sheet1
 A  B  C  D  E  F  G  H 
1  Country  Country name  Depot code  Depot name    Name  Depot Name 
2  GB  United Kingdom  STN  Stansted    United Kingdom  Stansted 
3  GB  United Kingdom  EDI  Edinburgh     Edinburgh 
4  GB  United Kingdom  EMA  East midlands     East midlands 
5  FR  France  GNO  Garanoa     
6  FR  France  MRS  Marseille     
7        United Kingdom  Stansted 
8         Edinburgh 
9         East midlands 
10        GB  Stansted 
11         Edinburgh 
12         East midlands 
13        France  Garanoa 
14         Marseille 
15        FR  Garanoa 
16         Marseille 
17         
Spreadsheet Formulas 
Cell  Formula  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)
Like this thread? Share it with others