Match Cell Values

[TropicalMagic]

Hi all,



I have two worksheets in the same workbook



The first screenshot is Worksheet A

The second screenshot is Worksheet B

Question:

How to match SIZE PERCENTAGES numbers according to the columns “Line”, “Sub-Line”, “Category”, “Sub-Category”, “Occasion” and “Size Pack”?



For example, Worksheet A Column “S1” J10 cell value should be 18%, following the tabular data demarcated in red borders in both Worksheets A and B.



Many thanks!

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Match Cell Values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

Do you still need help on this, or have you managed to sort it based on your other question?

 

[TropicalMagic]

Here is my solution:

=(VLOOKUP(CONCAT(B10,"-",C10,"-",E10,"-",F10,"-",D10,"-",L10),'Worksheet2'!$G$9:$O$44,MATCH('Worksheet1'!$O$9,'Worksheet2'!$G$9:$O$9,0),FALSE))*N10

Cheers

 

[Radoslaw Poprawski]

Here is another solution with Vlookup:

ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
	B	C	D	E
1	Region	Storehouse	Product	Code
2	East	M3	Lion	272x
3
4	Region	Storehouse	Product	Code
5	West	M2	Twix	769x
6	East	M2	Mars	409x
7	West	M3	Lion	367x
8	West	M1	Twix	901x
9	West	M2	Mars	901x
10	East	M1	Lion	237x
11	West	M3	Twix	827x
12	West	M3	Mars	649x
13	East	M3	Lion	272x
14	West	M1	Lion	447x
15	East	M2	Twix	347x
16	East	M2	Lion	340x
17	West	M2	Lion	584x
18	East	M1	Mars	225x
19	East	M3	Mars	487x
20	East	M1	Twix	128x
21	East	M3	Twix	179x
22	West	M1	Mars	474x
ex-285 zrobione

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(B2,IF((C5:C22=C2)*(D5:D22=D2),B5:E22,""),4,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E22	Expression	=AND(D5=$D$2;C5=$C$2;B5=$B$2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	East;West
C2	List	M1;M2;M3
D2	List	Mars;Lion;Twix

But you also can use spilled arrey thing:

ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
	B	C	D	E	F	G	H	I	J	K
1	Region	Storehouse	Product	Code
2	East	M3	Lion	272x	414	786	175	494
3
4	Region	Storehouse	Product	Code	sub-Code#1	sub-Code#2	sub-Code#3	sub-Code#4	sub-Code#5	sub-Code#6
5	West	M2	Twix	769x	483	434	122	154	989	780
6	East	M2	Mars	409x	120	647	157	544	939	450
7	West	M3	Lion	367x	921	911	536	692	771	733
8	West	M1	Twix	901x	261	850	638	974	660	856
9	West	M2	Mars	901x	439	939	536	544	446	189
10	East	M1	Lion	237x	703	663	760	686	875	512
11	West	M3	Twix	827x	559	792	649	660	168	472
12	West	M3	Mars	649x	766	942	282	805	237	210
13	East	M3	Lion	272x	414	786	175	668	890	494
14	West	M1	Lion	447x	586	770	712	566	106	994
15	East	M2	Twix	347x	489	247	189	307	358	469
16	East	M2	Lion	340x	901	810	357	399	495	282
17	West	M2	Lion	584x	148	937	748	251	216	926
18	East	M1	Mars	225x	219	379	147	292	444	940
19	East	M3	Mars	487x	567	913	579	766	744	923
20	East	M1	Twix	128x	859	932	750	634	833	897
21	East	M3	Twix	179x	419	869	243	237	922	337
22	West	M1	Mars	474x	423	594	124	567	523	334
Multiple Criteria VL

Cell Formulas
Range	Formula
E2:I2	E2	=VLOOKUP(B2,IF((C5:C22=C2)*(D5:D22=D2),B5:K22,""),{4,5,6,7,10},0)
F5:K22	F5	=RANDARRAY(18,6,100,999,TRUE)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:K22	Expression	=AND(D5=$D$2;C5=$C$2;B5=$B$2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	East;West
C2	List	M1;M2;M3
D2	List	Mars;Lion;Twix

Remember that this part: {4,5,6,7,10}
Can be substituted with cell range WITH values that correspond between min and max Value of column count.
in my example there are 10 columns so min is 1 and max is 10, also you can repeat column numbers if you wish so.