vlookup (or similar) results where there are mutliple matches

[kmfdm515]

Hello,

I'm trying to find something similar to a vlookup that will return multiple matching values and place them horizontally in the columns after the lookup value.

After doing some research, I found several examples like this attached worksheet example.

What I have not been able to find is how to adjust this array formula to allow for using multiple lookup values at once. For example, in the attached sheet, let's say we have 5000 rows of data in column B. As such, we move our lookup value B8 ("Pen") to D2 and then copy the array formulas from C8-E8 to E2-G2. This works fine. However, I would then like to be able to add more unique lookup values in column D, and then copy cells E2-G2 all the way down to return results for all of them.

As far as I can tell, this does not work because the last part of the first array formula COLUMN(A1)) increments to A2, A3, etc as you copy it down. I couldn't change it using an absolute cell reference with $ either - that doesn't seem to work in the array formula.

Does anyone know how to accomplish this?

[wenqq3]

Return-multiple-values-horizontally-edit.xls

I think your formula worked well. If the attached file is what you mean?

[kmfdm515]

that is perfect...what change did you make? or did i just mess something up trying to copy them down the page?

[wenqq3]

Yes, i did edit.
=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), COLUMN(A1)))

=INDEX($C$2:$C$6, SMALL(IF($B8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), COLUMN(A1)))

And i use IFERROR, so it wont't show #NUM!.