Formula to Extract a List of Matches

Dear Experts,

I need your help

I have a list of article numbers in column A. One in each cell.
Next to it I have its storage places in column B.

in an other sheet I am using a vlookup to find where the article belongs.

My problem is: One article may be twice or three times in the list with different storage place.

somehow I should be able to find all storage places but of course the vlookup finds the first one and aborts... the result shouldnt be necessary be in one cell...can be next to each other..


(I was experimenting with pivot too but couldnt solve the problem)



Could somebody help me?

Thank you ineed!!
best regards
PeterAttachment 615724Attachment 615725

here is a formula I used that you might be able to adjust...
=IFERROR(INDEX($B$1197:$B$1205,AGGREGATE(15,6,ROW($A$1:$A$1205)/($A$1197:$A$1205=C$1197),ROWS($A$1:A1))-(1-1),COLUMN()-(COLUMN()-1),1),"")

NOW, the range that the looked up value was returning is B1197:b1205 - which would be the range for your storage place.
Keep anything that begins in A1 or column 1 and adjust the other parts to your range. My match is C1197, so that would be your vlookup value, in your case the article. If it is elsewhere point to that cell.
Hopefully you can adjust to your needs and this isn't an array formula.

if you post a sample workbook you can get a more accurate formula. Just enough representative samples AND expected results so we can see what you are dealing with. Go advanced then manage attachments to upload a workbook.

Try this:

=WENNFEHLER(INDEX($B$1197:$B$1205;AGGREGAT(15;6;ZEILE($A$1:$A$1205)/($A$1197:$A$1205=C$1197);ZEILEN($A$1:A1))-(1-1);SPALTE()-(SPALTE()-1);1);"")

https://en.excel-translator.de/translator/

It's just a translation of the formula in post #2.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please read our rules regarding thread titles. I have changed yours for you this time, but next time please make sure you use a more descriptive one yourself.

Named ranges:
Article =Sheet1!$A$2:$A$13
ArticleNumbers =Sheet3!$A$2:$A$11
srchval =sheet2!$A$5
Storage =Sheet1!$B$2:$B$13

Please Login or Register  to view this content.

In B5 then drag across

=IFERROR(INDEX(Sheet1!$B$2:$B$13,AGGREGATE(15,6,ROW(Sheet1!$B$2:$B$13)/(Sheet1!$A$2:$A$13=$A5),COLUMNS($B5:B5))-ROW($B$2)+1),"")

Thanks for feed back.

Here is the corrected formula.Red Colour.
3 is the starting row number of data of verp!$B$3:$B$2500
=IFERROR(INDEX(verp!$B$3:$B$2500,AGGREGATE(15,6,ROW(verp!$B$3:$B$2500)/(verp!$A$3:$A$2500=$A4),COLUMNS($F4:F4))-ROW($B$3)+1),"")

Thanks for feedback