Compare more conditions from 2 tables and write right value

[Tibor GRENDEL]

Dear all,
I need update old price list with new prices. Old pricelist can´t to replace simply with new.

I need compare poduct_code1 (column A in table A2:F16) with product_code2 (column L in table L3:N23).
If table L3:N23 obtain A is in L I have to compare column D with column M and if D=M I have to write responsible value from N to F.
Step 1: compare value from column A:A with L:L
Step 2: if A=L compare D with M, if D=M write responsible value from N to F
Step 3: if A # L write value "No contain" to F

Test subor s par polozkami_2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	product_code1	old_date_start1	old_date_end2	MOQ1	old_price1	new_price1=new_price2 depending by product_code2 and MOQ2
2	101127	2021-01-01	2021-12-31	1	980	No contain	I need paste value from table L3:N23 when A = L and to D=M then F=N					product_code2	MOQ2	new_price2
3	101127	2021-01-01	2021-12-31	5	560	No contain	example					101090	1	1023,58
4	101131	2021-01-01	2021-12-31	1	400	780	example					101100	1	806,42
5	101131	2021-01-01	2021-12-31	5	350	50	example					101111	1	883,89
6	101133	2021-01-01	2021-12-31	1	993	1023	example					101127	1	1000
7	101133	2021-01-01	2021-12-31	5	929	957	example					101127	5	780
8	101139	2021-01-01	2021-12-31	1	1209,63							101131	1	600
9	101140	2021-01-01	2021-12-31	1	1306,62							101131	5	50
10	405085	2021-01-01	2021-12-31	3	119							101133	1	1023
11	405086	2021-01-01	2021-12-31	1	129							101133	5	957
12	405086	2021-01-01	2021-12-31	3	122							101159	1	1647
13	405087	2021-01-01	2021-12-31	1	183,34							101159	4	1618
14	405090	2021-01-01	2021-12-31	1	199							101160	1	1594
15	405094	2021-01-01	2021-12-31	1	126							101160	4	1618
16	405095	2021-01-01	2021-12-31	1	182							101162	1	2021,54
17												101163	1	2021,54
18												201435	1	212
19												201435	5	167
20												201523	1	201
21												201523	40	137
22												210162	1	61,6
23												210162	5	49,28
Sheet1

 

[Alex Blakenburg]

I am going to have to assume your example is wrong. eg you have 780 against 1001133 while in the table to the right it is against 1001127.
Also No contain on the first 2 lines looks wrong.

So unlesss I am misunderstanding the logic. Try this:

20220212 Lookup multiple criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	product_code1	old_date_start1	old_date_end2	MOQ1	old_price1	new_price1=new_price2 depending by product_code2 and MOQ2
2	101127	1/01/2021	31/12/2021	1	980	1000	I need paste value from table L3:N23 when A = L and to D=M then F=N					product_code2	MOQ2	new_price2
3	101127	1/01/2021	31/12/2021	5	560	780	example					101090	1	1023.58
4	101131	1/01/2021	31/12/2021	1	400	600	example					101100	1	806.42
5	101131	1/01/2021	31/12/2021	5	350	50	example					101111	1	883.89
6	101133	1/01/2021	31/12/2021	1	993	1023	example					101127	1	1000
7	101133	1/01/2021	31/12/2021	5	929	957	example					101127	5	780
8	101139	1/01/2021	31/12/2021	1	1209.63	No contain						101131	1	600
9	101140	1/01/2021	31/12/2021	1	1306.62	No contain						101131	5	50
10	405085	1/01/2021	31/12/2021	3	119	No contain						101133	1	1023
11	405086	1/01/2021	31/12/2021	1	129	No contain						101133	5	957
12	405086	1/01/2021	31/12/2021	3	122	No contain						101159	1	1647
13	405087	1/01/2021	31/12/2021	1	183.34	No contain						101159	4	1618
14	405090	1/01/2021	31/12/2021	1	199	No contain						101160	1	1594
15	405094	1/01/2021	31/12/2021	1	126	No contain						101160	4	1618
16	405095	1/01/2021	31/12/2021	1	182	No contain						101162	1	2021.54
17												101163	1	2021.54
18												201435	1	212
19												201435	5	167
20												201523	1	201
21												201523	40	137
22												210162	1	61.6
23												210162	5	49.28
Answer

Cell Formulas
Range	Formula
F2:F16	F2	=IFNA(LOOKUP(2,1/(($L$3:$L$23=A2)*($M$3:$M$23=D2)),$N$3:$N$23),"No contain")

 

[Tibor GRENDEL]

Dear Alex,
thank you! It´s working right.
Have a nice day

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.