Question on Vlookupnth

[crunchtime9999]

Hello,
I used the following function code from the board to return multiple values for a vlookup.
I looked on the forum for how to use code tags but could not find so please forgive me for not being able to do this. (Please let me know how to do the code tags)

code:
Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Double, _
Optional Nth As Double = 1)
'Similar to Vlookup, but returns the Nth value found from the top of myrange.
'Not necessarily the First.
'The 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

Dim Count, i As Long
Count = 0
If ColRef = 0 Then ColRef = MyRange.Columns.Count
For i = 1 To MyRange.Rows.Count
If MyRange.Cells(i, 1).Value = MyVal Then
Count = Count + 1
If Count = Nth Then
VlookupNth = MyRange.Cells(i, ColRef).Value
Exit Function
End If
End If
Next i
VlookupNth = "Not Found"
End Function
code:\

I tried the formula and the results did not come out as I expected. Can someone here help me to understand what I am doing wrong.

aging extra info test.xls
	A	B	C	D
1	OrderNumber	Productcode	Warehouse
2	501567	LBV6765	North15
3	501619	HRY654	North23
4	501619	LO0987	East65
5	501619	D4532	West12
6	501619	FRE456	East25
7	501619	WES222	West99
8	501622	POI890	Central77
9
10	Test
11	501567	LBV6765	North15
12	501619	LO0987	East65
13	501619	D4532	West12
14	501619	FRE456	East25
15	501619	WES222	West99
16	501619	NotFound	NotFound
17	501622	NotFound	NotFound
Sheet1

 

[Jonmo1]

This part of the formula
Row(A1) is determining which occurance to return, the 1st 2nd 3rd etc...

in B11, Row(A1) = 1
When you fill that formula down, A1 incriments to A2
So in B12, it is now Row(A2) so it returns the 2nd occurance, not the first.

So at each Change in lookup value A11:A17, start it over at 1.

Try using this formula without the complicated Row() and COLUMN() Functions.
It's a good idea to understand what the formula does first, then try to make it automated and easier..

in B11
=vlookupnth($A11,$A$1:$C$8,2,1)
Copy Paste that formula Down and Right through your whole range B11:C17)
In column B, change all the 2's to 3s..

In All formulas, Change the 1 to whatever is the Nth Occurance you want returned...

 

[crunchtime9999]

Hello,
Thanks for the reply. I will try this a work in the morning and let you know how it turns out.

Thanks again