Vlookup?

Hi, and welcome to the forum.

A Vlookup, indeed any lookup will only return one value. Rather than using lookup you should be using either an Autofilter or an Advanced Filter.

Try this...

Data Range

	A	B	C	D	E	F	G	H
1	A111-765	round	yes		A111	A111-765	round	yes
2	564	square	yes			A111-76	rectangle	yes
3	14799	rectangle	no			A111-90	rectangle	yes
4	2167	round	yes
5	A111-76	rectangle	yes
6	A111-90	rectangle	yes
7	A112-53	rectangle	no
8	------	------	------	------	------	------	------	------

This array formula** entered in F1:

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(FIND(E$1,A$1:A$7)),ROW(A$1:A$7)),ROWS(F$1:F1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula entered in G1 and copied across to H1:

=IF($F1="","",VLOOKUP($F1,$A$1:$C$7,COLUMNS($G$1:G$1)+1,0))

Then, select F1:H1 and copy down until you get blanks.

Originally Posted by annac113

When I type in the formula, I need to press CTRL,SHIFT at the same time and then hit ENTER? This is the first time I ever had to do that? Why is that the case?

Because that formula is an array formula and that's how you have to enter array formulas. For a more specific answer as to why, you'd have to ask Microsoft!

Here's a small sample file that demonstrates this. I put the raw data on one sheet and the extracted data on another sheet. I also included column headers in the example.

With duplicate codes like A111-765 VLOOKUP will always find the first instance.

In that case we can just use the array formula copied across.

Here's the updated file...

You're welcome. Thanks for the feedback!