Matching Values in Tables

BeppeBM

New Member
Joined
Jun 11, 2019
Messages
2
[FONT=&quot]I have a MAIN empty table (for example 6 rows and 6 columns, see table below) and a SMALL table which is a subset of the main one (say 2 rows and 3 columns) and contains data. What formula I can use in the MAIN table so that each cell gets populated with the corresponding values from the SMALL table, living blanks all the cells which don't exist in the SMALL one (in the example below cell named "Yellow-Mon" in the MAIN table will be left blank since it doesn't exist in the SMALL table, while cell "Green-Sat" will be populated with the number 0.6 from the corresponding cell in the SMALL table). Thanks[/FONT]
[FONT=&quot]
[/FONT]

[FONT=&quot]MAIN Table[/FONT]
MonTueWedThuFriSat
Yellow
Red
Blue
Green
Pink
White

<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>
[FONT=&quot]
[/FONT]

[FONT=&quot]SMALL Table[/FONT]
[FONT=&quot]
[/FONT]

TueFriSat
Red0.50.90.77
Green0.30.450.6

<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
try PowerQuery

ColorMonTueWedThuFriSatColorTueFriSat
YellowRed
0.5​
0.9​
0.77​
RedGreen
0.3​
0.45​
0.6​
Blue
Green
Pink
White
ColorMonTueWedThuFriSat
Blue
Green
0.3​
0.45​
0.6​
Pink
Red
0.5​
0.9​
0.77​
White
Yellow

Code:
[SIZE=1]// Append1
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace1 = Table.ReplaceValue(Source1,null,"x",Replacer.ReplaceValue,{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}),
    UnPivot1 = Table.UnpivotOtherColumns(Replace1, {"Color"}, "Attribute", "Value"),
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    UnPivot2 = Table.UnpivotOtherColumns(Source2, {"Color"}, "Attribute", "Value"),
    Source = Table.Combine({UnPivot1, UnPivot2}),
    Replace = Table.ReplaceValue(Source,"x",null,Replacer.ReplaceValue,{"Value"}),
    Pivot = Table.Pivot(Replace, List.Distinct(Replace[Attribute]), "Attribute", "Value", List.Sum)
in
    Pivot[/SIZE]
 
Upvote 0
You'll have to extend your match ranges as your dataset gets bigger


Book1
ABCDEFG
1MAIN Table
2MonTueWedThuFriSat
3Yellow      
4Red0.50.90.77
5Blue
6Green0.30.450.6
7Pink
8White
9
10
11
12
13
14SMALL Table
15TueFriSat
16Red0.50.90.77
17Green0.30.450.6
Sheet1
Cell Formulas
RangeFormula
B3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(B$2,$B$15:$D$15,0)),"")
C3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(C$2,$B$15:$D$15,0)),"")
D3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(D$2,$B$15:$D$15,0)),"")
E3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(E$2,$B$15:$D$15,0)),"")
F3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(F$2,$B$15:$D$15,0)),"")
G3=IFERROR(INDEX($B$16:$D$17,MATCH($A3,$A$16:$A$17,0),MATCH(G$2,$B$15:$D$15,0)),"")
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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