VLOOKUP more than one instance

[Report_2]

-----A-----B-----C
(1) 5280 5,000 1/01/03
(2) 5280 6,000 2/01/03
(3) 5280 9,000 5/01/03

If I use a VLOOKUP for the above data it will return the value of the first found instance of 5280 of 5,000 on 1/01/03 from Row 1.

I looked in the Excel Help file but found limited advise.

Is it possible to modify or nest the VLOOKUP to find the second instance and/or even the third? If so, can you provide an example?

My spreadsheet contains 780 rows involving over 50 different dates but for each item listed in column A there are no more than 4 instances of like individual items.

Perhaps an "IF" statement nested in the VLOOKUP?

I suspect it simply can not be done but it sure seems like it should be possible. I do not know how.

[smurfy]

What do you want to do with the returned matches?
If you just want to list them I'll need to think about it and have a play tonight when I get home from work.

If you just want to add them up, you're better off using a SUMIF.
E.G.
=SUMIF(A1:A780,5280,B1:B780)
Will give you the sum of all entries in B where the corresponding entry in A is your 5280 code number.

[Report_2]

I am looking up from a different spreadsheet.

The 5280 is a style number of one of 385 products we carry.

The 780 rows contain all of our style's, unshipped purchases, from the vendors. It is a vendor order report.

The speadsheet that I want to update contains current inventory, intransit inventories, this month's, next month's and future placed orders. It is a sales inventory report.

I want to return the quantities and dates of arrival from the vendor orders report to determine my inventory levels against the future order dates into the sales inventory report.

Using the VLOOKUP I can return the first or next arriving order but not the second or third purchase order date and quantity.

Ultimately, I need to determine if I need to air freight the order versus waiting on an ocean vessel. 10 days versus 30 days arrival against the upcoming orders.

Our customer service department likes to take orders against styles that are not in stock.

I present the above with much verbiage to enable a better understanding of my goal.

[Report_2]

I have found my solution using a modified 'User Defined Function'
=FindNth($A$2:$C$6,A2,1,2)

To create the UDF I used the below code:


Function FindNth(Table As Range, Val1 As Variant, Val1Occrnce As Integer, _
ResultCol As Integer)

'''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com

'Finds the N'th value in the first Column of a table that has a stated _
value on the same row in another Column.
'''''''''''''''''''''''''''''''''''''''

Dim i As Integer
Dim iCount As Integer
Dim rCol As Range

For i = 1 To Table.Rows.Count
If Table.Cells(i, 1) = Val1 Then
iCount = iCount + 1
End If

If iCount = Val1Occrnce Then
FindNth = Table.Cells(i, ResultCol)
Exit For
End If
Next i
End Function

Thanks to Bill in SD, CA for the lead.

Smurfy, I thank you equally for your attention, inspiration and feedback.

[smurfy]

Very nice.
I hadn't had a chance to look into it yet.
I remember doing something like this a while ago (when i was still using XL97) but couldn't remember how I did it.
It wasn't a UDF but at least now I can dedicate those brain cells to something else

[Report_2]

At home I am using XL97 and find the "User Defined" option within the Paste Funcion button but I do not remember seeing it on my work's XL2K. Is this not available in XL2K?

I got the UDF to work on my home machine but it is the work machine that I t need it for.