Index Formula Help

[Lsxtrkiller]

I am trying to get an index formula to work and achieve the following:

I will have one sheet with a lot of data: part number(A) location(b) and qty(c) that are the important columns for this project.

I will have a search sheet part number(A) location(b) and qty(c).

Part Number 123 entered into ("A2") could have 9 locations and 9 qty so what I would like to do is use an index formula to return all the matching values from the master sheet for columns (B) and (C).

I know it should be an easy index formula within an iferror that I could just drag down and should populate all results I just cant get it to work

Please advise
Test.xlsx

[AlKey]

On Sheet2 of your spreadsheet enter Part# in cell D1 123
then enter array formula in A2 and copy acorss and then down

Please Login or Register  to view this content.

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[AliGW]

This should help:

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Part Number	Location	QTY	123
2	123	S01-T01	25
3	123	S01-T02	26
4	123	S01-T03	27
5	123	S01-T04	28

Sheet: Sheet2

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Part Number	Location	QTY	123
2	=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:1))),"")	=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:1))),"")	=IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:1))),"")
3	=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:2))),"")	=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:2))),"")	=IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:2))),"")
4	=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:3))),"")	=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:3))),"")	=IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:3))),"")
5	=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:4))),"")	=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:4))),"")	=IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$D$1,ROW(Sheet1!$A$2:$A$8)),ROWS($1:4))),"")

Sheet: Sheet2

[Lsxtrkiller]

Thank you both!

[AliGW]

You're welcome!

[AlKey]

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).