Find all instances of a selected item and return all results

Basically what the title reads.

I would like to use Vlookup for all the items found in a dropdown menu. I can only return the same value.
Vlookup may not be the function for this, I just don't know what else to use.

Please look at the attached workbook. I only want to use formulas for this.

Thank you for your time

Hi NewGuy OnBlock and welcome to the forum.

You need to make your list a table or add auto filter dropdowns. Then you will click on the Item dropdown and select the one you want. After doing this only rows with that selected item will show.

see the attached and dropdown the item header.

Is that what you wanted? No code and no formulas either!!

How about Advanced Filters to a second sheet? You may need to do a named range of your data to make it easier.

You said formulas only. It is very easy with a little code (if you can code). Without code you type the value you want in the criteria area and run the Advanced Filter again.

Ok look at this file.

I created an Advanced Filter on the sheet and did a very quick macro to copy the value you double click on and then advance filter it. Easy Peasy.

See if this works for you. Double click on any of the items to see filtered area change.

Thanks Marvin,
I know vba can easily be used for this, just loop through the column and when an item matches, return the item beside it.
I want the user to be able to open a workbook that is macro free, select from the drop down menu and the results will appear where the formulas are.

BTW, your example using VBA is reversed from what I am looking for.

I think you're going to need some code to do what you want.

Maybe one of the smart forum guru's can help with this one.

Do you want to "look up" companies or items?

Thanks ChemistB,

If you click on the data validation in A2, all the items matching that selection dill display. Like Vlookup such as in the sample workbook, but with all the instances appearing.

Try this workbook.

Select the Company from the drop-down in A2 (yellow)

The helper column can be hidden, use the group button in this example ( the +/- button below the formula bar)

The formula in B3 is the key to this sheet

=IF(ROW()-ROW($B$3:$B$1000)+1>ROWS($A$3:$A$1000)-COUNTBLANK($A$3:$A$1000),"",INDIRECT(ADDRESS(SMALL((IF($A$3:$A$1000<>"",ROW($A$3:$A$1000),ROW()+ROWS($A$3:$A$1000))),ROW()-ROW($B$3:$B$1000)+1),COLUMN($A$3:$A$1000),4)))

This is an array formula and must be confirmed with Ctrl+Shift+Enter , not just enter.

The named ranges I have used are all dynamic, so no need to change as your data range grows.

I have added a List of "companies" on a separate sheet, add to / subtract from this as new customers become available.
Just remember to keep the list continuous, i.e. no blanks.
This table could be on Sheets("Data").

Hope this helps

Ahh possible
=IF(B3="","",INDIRECT("Data!D"&B3))