Multiple returns from vlookup

[Mike Moon]

I'm trying to get a function that will allow me to pull up multiple entries
from a single vlookup (or multiple lookups perhaps that would pull sequential
records)

So lets say I have a worksheet with:

A B C
Date Name Invoice Number

I need the last 5 invoices from name X to display on another worksheet in
seperate cells.

Ideas? I've tried the User Defined vlookups tool I found online and couldn't
get it to work (and yes I've Cntrl-Shift enter to make it an array).

 

[smartin]

I'm trying to get a function that will allow me to pull up multiple entries
from a single vlookup (or multiple lookups perhaps that would pull sequential
records)

So lets say I have a worksheet with:

A B C
Date Name Invoice Number

I need the last 5 invoices from name X to display on another worksheet in
seperate cells.

Ideas? I've tried the User Defined vlookups tool I found online and couldn't
get it to work (and yes I've Cntrl-Shift enter to make it an array).

Here is a way using some helper columns. Not sure this is the best way,
but it works.

I assumed 24 rows of data with Date in ascending order and a variety of
Names including the target "X".

I added a helper column in Sheet1, column D, call it "remaining counts":
=COUNTIF(B2:$B$25,"X")

Over to Sheet2 now, column A is another helper:

position
5
4
3
2
1

And column B has the goal:
=INDEX(Sheet1!$A$2:$C$25,MATCH(1,(Sheet1!$B$2:$B$25="X")*(Sheet1!$D$2:$D$25=A2),0),3)

entered as an array in B2 by pressing Ctrl+Shift+Enter.