Return nth values in a lookup

[dracer]

Hi there. I have a table with the customer number in column A and in Column B is the note type. There could be more than 1 note type so the customer number would appear more than once in column A. A Vlookup will only return the first value to the right. How can I return the second and third values in column B that are below that first value. I presume I need an array formula that would include more than one cell. I'm stumped by this. Does anyone have any suggestions?

 

[ParamRay]

I don't know if this is the best formula out there, but it does appear to work.

Suppose you're looking for "CUST999" in column A. Then try this array formula:

{=INDEX(B:B,LARGE((A:A="CUST999")*ROW(A:A),1+SUM(N(A:A="CUST999"))-1))}

At the moment, the formula is equivalent to a VLOOKUP. However, if you change the number highlighted in red to 2, then it will return the second instance; if you change it to 3, then it will return the third instance; and so on...