presenting a value if the cell on one sheet matches multiple criteria on another

[nlweqfvneviepuctqm]

I have an order form that contains a load of products- product 1, 2, 3, 4 etc. I have the names of the orders that they appear on and the date that the order is expected. On a separate sheet all the orders are in a big long list. I need a formula that looks at the product name, ensures it is on the same row as both the appropriate order name and order date- then relays the amount on order. example spreadsheet attached.

any help greatly appreciated.

example spreadsheet - https://easyupload.io/h4lfpc

 

[Alex Blakenburg]

Try this:-

example.xlsx
	A	B	C	D	E	F	G
1	NAME OF ORDER	order from france	order from spain	2nd order from france	order from france	order from spain	2nd order from france
2	DATE OF ORDER	1/10/2021	1/10/2021	15/10/2021	15/10/2021	1/11/2021	1/11/2021
3	product 1	10
4	product 2		20
5	product 3			30
REPORT (2)

Cell Formulas
Range	Formula
B3:G5	B3	=IFERROR(LOOKUP(2,1/((ORDERS!$B$2:$B$25=B$1)*(ORDERS!$A$2:$A$25=B$2)*(ORDERS!$C$2:$C$25=$A3)),ORDERS!$D$2:$D$25),"")

 

[nlweqfvneviepuctqm]

This worked-

Can you tell me how to amend this formula so that it doesn't take into account the order reference in cell B1.
the bit in bold is unnecessary
=IFERROR(LOOKUP(2,1/((ORDERS!$B$2:$B$25=B$1)*(ORDERS!$A$2:$A$25=B$2)*(ORDERS!$C$2:$C$25=$A3)),ORDERS!$D$2:$D$25),"")
new example spreadsheet here-

 

[Alex Blakenburg]

You seem to have 2 different questions here.
The lookup was in response to the question with a workbook that looked like the below image.

In that context removing one of the criteria doesn't make sense.
If you really did want drop the Name of Order criteria the formula would like like this.

=IFERROR(LOOKUP(2,1/((ORDERS!$A$2:$A$25=B$2)*(ORDERS!$C$2:$C$25=$A3)),ORDERS!$D$2:$D$25),"")

If you want an explanation of how it works you can find one here:-
Excel LOOKUP Function Examples with Video and Workbook

 

[Alex Blakenburg]

new example spreadsheet here-

Like I said dropping the criteria with the previous spreadsheet provided didn't make sense but your "new example" is a different layout and calls for a summing of the values and not to return a specific Order Name value.
I think you tried to open a new thread here, but unfortunately the wording was not clear enough to distinguish it from this thread and it was closed.
presenting/totalling a value if the cell on one sheet matches multiple criteria on another

For that you can try this:

example(1).xlsx
	A	B	C	D	E	F	G
1	code	PO1	PO2	PO3	PO4	PO5	PO6
2		DATES
3		14/10/2021 00:00	26/10/2021 00:00	28/10/2021 00:00	01/11/2021 00:00	02/11/2021 00:00	03/11/2021 00:00
4
5	product 1	123	0	21	35	20	0
6	product 2	199	4	0	12	31	0
7	product 3	174	1	1	43	2	0
Sheet1

Cell Formulas
Range	Formula
B5:G7	B5	=SUMIFS(Sheet2!$D$2:$D$20,Sheet2!$B$2:$B$20,$A5,Sheet2!$C$2:$C$20,B$3)