Search and return multiple values

[craigfer]

We have data sets on three seperate sheets within Excel

We have a ORDER STATUS (Sheet named STATUS). The STATUS sheet contains all the order numbers as unique values.

ORDER NUMBER	STATUS	AREA	COMMENTS
100103637​	WSCH	UNDER PREPARATION	INVESTIGATE
100000483​	WSCH	UNDER PREPARATION	SEND
100180611​	SCH	UNDER PREPARATION	SEND
100226506​	WSCH	UNDER PREPARATION	INVESTIGATE

Order Numbers (Sheet named ON) which can contain one or more ITEM

ORDER NUMBER	ITEM
100001575	11133628
100009849	40006676
100009849	40006833
100018330	11170308
100018330	40006398
100018855	40013378
100018855	11033495
100020763	40002159
100020763	40002160
100020763	40002161
100020763	40002162

We have a list of STOCK (Sheet name STOCK)

ITEM	DESC	STOCK
40002159​	SEAL	17​
40002160​	ORING	0​
40002161​	ORING	37​
40002162​	ORING	16​
11170308​	GLOBE	0​
40006398​	GSKT	379​
40013378​	GSKT	25​
11033495​	PIPE CLAMP	4​

I want to be able to PASTE the orders number from STATUS in a new Sheet and produce headings like this. I also want the formula to return multiple values if the ORDER NUMBER has more than one material.
For the work to be completed we need stock of all the ITEM. Anything with a 0 value means we cannot complete the work for that ORDER NUMBER.
In the example below 40002160 has 0 stock so we cannot complete.

PASTE ORDER NUMBER *Paste in CELL A2*	STATUS	AREA	COMMENT	ITEM	DESC	STOCK	CAN COMPLETE ORDER
100020763​	WSCH	UNDER PREPARATION	INVESTIGATE	40002159​	SEAL	17​	NO
100020763​	WSCH	UNDER PREPARATION	INVESTIGATE	40002160​	ORING	0​	NO
100020763​	WSCH	UNDER PREPARATION	INVESTIGATE	40002161​	ORING	37​	NO
100020763​	WSCH	UNDER PREPARATION	INVESTIGATE	40002162​	ORING	16​	NO

If STOCK is greater than 0 then it should return YES

 

[jdellasala]

Please use XL2BB when posting data.
Anyway, you want to start with turning your data into Excel Tables like this:
Table Name: Status:

Book1
	A	B	C	D
1	ORDER NUMBER	STATUS	AREA	COMMENTS
2	100103637	WSCH	UNDER PREPARATION	INVESTIGATE
3	100000483	WSCH	UNDER PREPARATION	SEND
4	100180611	SCH	UNDER PREPARATION	SEND
5	100226506	WSCH	UNDER PREPARATION	INVESTIGATE
STATUS

Table Name: OrderNumber:

Book1
	A	B
1	ORDER NUMBER	ITEM
2	100001575	11133628
3	100009849	40006676
4	100009849	40006833
5	100018330	11170308
6	100018330	40006398
7	100018855	40013378
8	100018855	11033495
9	100020763	40002159
10	100020763	40002160
11	100020763	40002161
12	100020763	40002162
13	100103637	40002163
ON

Table Name: Stock:

Book1
	A	B	C
1	ITEM	DESC	STOCK
2	40002159	SEAL	17
3	40002160	ORING	0
4	40002161	ORING	37
5	40002162	ORING	16
6	11170308	GLOBE	0
7	40006398	GSKT	379
8	40013378	GSKT	25
9	11033495	PIPE CLAMP	4
10	40002163	Plgr	100
STOCK

And Table Name Report:

Book1
	A	B	C	D	E	F	G	H
1	Order Number	Status	Area	Comment	Item	Desc	Stock	Can Complete Order?
2	100103637	WSCH	UNDER PREPARATION	INVESTIGATE	40002163	Plgr	100	Yes
3	100000483	WSCH	UNDER PREPARATION	SEND	Not Found			NO
4	100180611	SCH	UNDER PREPARATION	SEND	Not Found			NO
5	100226506	WSCH	UNDER PREPARATION	INVESTIGATE	Not Found			NO
Report

Cell Formulas
Range	Formula
B2:B5	B2	=IF([@[Order Number]]<>"",XLOOKUP([@[Order Number]],Status[ORDER NUMBER],Status[STATUS],"Not Found"),"")
C2:C5	C2	=IF([@[Order Number]]<>"",XLOOKUP([@[Order Number]],Status[ORDER NUMBER],Status[AREA],"Not Found"),"")
D2:D5	D2	=IF([@[Order Number]]<>"",XLOOKUP([@[Order Number]],Status[ORDER NUMBER],Status[COMMENTS],"Not Found"),"")
E2:E5	E2	=IF([@[Order Number]]<>"",XLOOKUP([@[Order Number]],OrderNumber[ORDER NUMBER],OrderNumber[ITEM],"Not Found"),"")
F2:F5	F2	=IF(ISNUMBER([@Item]),XLOOKUP([@Item],Stock[ITEM],Stock[DESC],"Not Found"),"")
G2:G5	G2	=IF(ISNUMBER([@Item]),XLOOKUP([@Item],Stock[ITEM],Stock[STOCK],"Not Found"),"")
H2:H5	H2	=IFS(AND(ISNUMBER([@Stock]),[@Stock]>0),"Yes",AND(ISNUMBER([@Stock]),[@Stock]<=0),"NO",[@Item]="Not Found","NO",TRUE,"")

It is VERY IMPORTANT that the numbers entered are actually numbers and not text. Note that the data in all the Order Number and Item columns is RIGHT JUSTIFIED. That is not formatting, that is how Excel displays numeric data including dates and times. If they were text as it LOOKS like they are in your ON table they would be LEFT JUSTIFIED without any formatting. Just to complete that thought, Errors and other special values like TRUE and FALSE are centered by Excel.
While it is not important whether or not the first three tables are Excel Tables, the Report table MUST be an official Excel Table. Note that if you copy the tables above, you will have to turn them into tables manually and name them appropriately for the Report table to work. I have added Paste and Match Destination Formatting to my QAT which makes pasting XL2BB data much cleaner. Aside from the formatting which you don't want in this case, for some reason it pastes everything to Wrap which is annoying. That paste option is not available when you copy XL2BB data.
As you'll find once you get that all set up, no "template" is needed. The Report table will add new rows and copy the formulas when a new row is added - which will happen when you enter data into the next table row in column A or have your cursor in the last row of column H and hit [Tab].
And BTW, NONE of the Order Numbers in the Status Table are in the ON table you posted. I had to add that one entry to see the formulas work. Items in the OrderNumber and Stock tables that I added manually are in Bold Red and why all the items are "Not found" except the item I added manually in the report.
And the Table Names are in Upper/Lower case because it makes them easier to read and I don't like Excel to YELL AT ME! (LOL)