Need Vlookup function to pick up more than one record

Hi,

You could try this.

The formula needs to be entered with Ctrl + Shift + enter in D2 before dragged down.

When you adjust the ranges to fit your layout; note that the range for the Index function always must start at row one wherever your data is.

Sorry I did not understand your answer, can you please give it another shot.

Thanks.

What I suggested was a formula.

If your data was like in the exibit above it would look like:

=INDEX($B$1:$B$11,SMALL(IF($A$2:$A$11=$C$2,ROW($A$2:$A$11)),ROW()-ROW($D$1)))

Obviously it does not but as you have not specified your ranges you need to adjust the formula yourself.

Remember:

The formula needs to be entered with Ctrl + Shift + enter in D2 before dragged down.

When you adjust the ranges to fit your layout; note that the range for the Index function always must start at row one wherever your data is.

It does not seem to do what I need. Maybe I did not explain it properly.

Sheet 1 has a value that I am looking up on sheet 2. The problem is that the value I am looking up ie. 542A has three records affiliated with it ie. jane, Ann and Joe.

I want the function to bring all three back into my first sheet and list it down the column. As of now I am using a vlookup that just brings back Jane and if I copy it down it just lists Jane not Ann and Joe.

I hope this makes sense, I may not be able to do it with a Vlookup.

Thanks again for all you help

Please look closer at what I have posted. As I understand it does exactly what you ask for.

Still not working for me. Everytime I specify a different worksheet I receive the error #NA.

I don't know what I am doing wrong.

Thanks

Correction the error I receive is #num.

Is it because the data is on a separate worksheet, not on the same one as your example?

It would be so much easier if you could specify your ranges and I could type out the formula for you.

That it is a different sheet should not matter. But if you forget to confirm the formula with Ctrl + shift + enter instead of just enter it will not work.

Here I replace the ranges with al etter to explain what I mean:

=INDEX(A,SMALL(IF(B=C,ROW(B)),ROW()-ROW(D)))

A -- is the column with the values you want the formula to return This range must always start in row one even if your data starts lower.

B -- is the column to search for the lookup values

C -- is the one cell with the lookup value

D -- is a cell one row above wher you place the first formula (it does not matter what is in that cell)


It is important that you make all references absolute e.g. $C$2

If you cannot get this to work you need to post your sheet or clearly describe your layout.

Thank you so very much for your help, it works great!