Match Cell Values

TropicalMagic

New Member
Joined
Jun 19, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Hi all,



I have two worksheets in the same workbook



The first screenshot is Worksheet A

3A.png


The second screenshot is Worksheet B


4A.png



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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
Do you still need help on this, or have you managed to sort it based on your other question?
 
Upvote 0
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
 
Upvote 0
Solution
Here is another solution with Vlookup:
ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
BCDE
1RegionStorehouseProductCode
2EastM3Lion272x
3
4RegionStorehouseProductCode
5WestM2Twix769x
6EastM2Mars409x
7WestM3Lion367x
8WestM1Twix901x
9WestM2Mars901x
10EastM1Lion237x
11WestM3Twix827x
12WestM3Mars649x
13EastM3Lion272x
14WestM1Lion447x
15EastM2Twix347x
16EastM2Lion340x
17WestM2Lion584x
18EastM1Mars225x
19EastM3Mars487x
20EastM1Twix128x
21EastM3Twix179x
22WestM1Mars474x
ex-285 zrobione
Cell Formulas
RangeFormula
E2E2=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
CellConditionCell FormatStop If True
E5:E22Expression=AND(D5=$D$2;C5=$C$2;B5=$B$2)textNO
Cells with Data Validation
CellAllowCriteria
B2ListEast;West
C2ListM1;M2;M3
D2ListMars;Lion;Twix


But you also can use spilled arrey thing:

ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
BCDEFGHIJK
1RegionStorehouseProductCode
2EastM3Lion272x414786175494
3
4RegionStorehouseProductCodesub-Code#1sub-Code#2sub-Code#3sub-Code#4sub-Code#5sub-Code#6
5WestM2Twix769x483434122154989780
6EastM2Mars409x120647157544939450
7WestM3Lion367x921911536692771733
8WestM1Twix901x261850638974660856
9WestM2Mars901x439939536544446189
10EastM1Lion237x703663760686875512
11WestM3Twix827x559792649660168472
12WestM3Mars649x766942282805237210
13EastM3Lion272x414786175668890494
14WestM1Lion447x586770712566106994
15EastM2Twix347x489247189307358469
16EastM2Lion340x901810357399495282
17WestM2Lion584x148937748251216926
18EastM1Mars225x219379147292444940
19EastM3Mars487x567913579766744923
20EastM1Twix128x859932750634833897
21EastM3Twix179x419869243237922337
22WestM1Mars474x423594124567523334
Multiple Criteria VL
Cell Formulas
RangeFormula
E2:I2E2=VLOOKUP(B2,IF((C5:C22=C2)*(D5:D22=D2),B5:K22,""),{4,5,6,7,10},0)
F5:K22F5=RANDARRAY(18,6,100,999,TRUE)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E5:K22Expression=AND(D5=$D$2;C5=$C$2;B5=$B$2)textNO
Cells with Data Validation
CellAllowCriteria
B2ListEast;West
C2ListM1;M2;M3
D2ListMars;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.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top